We help IT Professionals succeed at work.

SQL check for value if exists set value to variable

Sheritlw
Sheritlw asked
on
I'm not sure how I would do this, but in a stored procedure I would like to perform a check on a value and if the value exists set the value to a variable.

Kind of like the following...

if exists(select prodimpid from producttypeslu where prodimpid = @impProdCatID)
 select @BrandID = BrandID
 
else
 Do an import and then
Select @BrandID = Scope_Identity()

I hope this makes since.

Thanks

if exists(select prodimpid from brandslu where where prodimpid = @impprodlineid)
		select @BrandID = brandid from brandslu where prodimpid = @impprodlineid
	else

		Insert into BrandsLU(UserID, BrandName)
		 select @UserID, ProductLine from ImportProductLine where 
		impProdLineID = @impProdLineID
		
		Select @BrandID = Scope_Identity()

Open in new window

Comment
Watch Question

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
One way...

Declare @count int

SELECT @count = COUNT(prodimpid) from producttypeslu where prodimpid = @impProdCatID

IF @COUNT > 0
   begin
   @BrandID = BrandID
   end
ELSE
   begin
   -- Do an import and then
   @BrandID = Scope_Identity()
   end

Author

Commented:

But wouldn't I have to do another select in order to get the brandid?
I was hoping I could set the variable without having to do another select.

SELECT @count = COUNT(prodimpid) from producttypeslu where prodimpid = @impProdCatID

IF @COUNT > 0
   begin
   Select @BrandID = BrandID from producttypeslu where prodimpid = @impProdCatID
   end
ELSE
   begin
   -- Do an import and then
   @BrandID = Scope_Identity()
   end
Top Expert 2011
Commented:
like this....
select @BrandID = brandid from brandslu where prodimpid = @impprodlineid
    	if @@rowcount=0
     begin
		Insert into BrandsLU(UserID, BrandName)
		 select @UserID, ProductLine from ImportProductLine where 
		impProdLineID = @impProdLineID
		
		Select @BrandID = Scope_Identity()
    end

Open in new window

Author

Commented:
Thank you