Check If Value Exists In Table

Question

I am needing a way to run an If/Else statement in SQL Server. This is what I am needing done, if my Exec sp_executesql @SQL statement returns a value (meaning it exists in the table) I want to set the value returned to a variable. If it does not return a value, then I want to print a message saying it does not exist.

With the sample DDL Below, I would want to assign the value of 3 to the variable @placeholder since 3 would be the value returned. How would that syntax be constructed?

Create Table #Test1 (id int,name varchar(100))
Insert Into #Test1 Values (1, 'Blue'), (2, 'Red'), (3, 'Pink'), (4, 'Orange')
Declare @Color varchar(100), @sql nvarchar(max), @placeholder varchar(100)
Set @Color = 'Pink'
Set @Sql = 'Select id from #Test1 WHERE name IN ('''+@Color+N''')'
Exec sp_executesql @SQL
Drop Table #Test1

• ypercubeᵀᴹ: Why the dynamic SQL? Why not somethign simple like SELECT @place_holder = id FROM #test1 WHERE name = @color; ?
• SmallFries BigGuys: @ypercubeTM - I thought that anytime you were using a variable in your syntax you must use dynamic sql. Your example above shows I need to do some further reading on variables!
• Andriy M: Does your actual code use a string variable to store an integer result too, as in this example? Seems to make little sense to me (definitely makes no sense for the purpose of the example).

Answer 1

Your example looks quite simple and does not need sp_executesql statement (as long as your query returns only one row)...

You can set your variable in your select :

Create Table #Test1 (id int,name varchar(100))
Insert Into #Test1 Values (1, 'Blue'), (2, 'Red'), (3, 'Pink'), (4, 'Orange')
Declare @Color varchar(100), @returned_id varchar(100)
Set @Color = 'Pink'
Select @returned_id = id 
from #Test1 
WHERE name = @Color
if @returned_id is null
 print 'Error - no rows returned for ' + @Color
else 
 print @returned_id
Drop Table #Test1

• SmallFries BigGuys: I thought that anytime you were using a variable in your syntax you must use dynamic sql. Your example above shows I need to do some further reading on variables!

Answer 2

You can also try to below approach. ISNULL is more convenient that if-else structure. But this approach will only work when you want to compare the result with NULL.

Create Table #Test1 (id int,name varchar(100));
Insert Into #Test1 Values (1, 'Blue'), (2, 'Red'), (3, 'Pink'), (4, 'Orange');
Declare @Color varchar(100), @returned_id varchar(100);
Set @Color = 'Pink';
Select 
 @returned_id = ISNULL(id, 'Error - no rows returned for ' + @Color)
from #Test1 
WHERE name = @Color
print @returned_id

• Andriy M: This just won't compile. And why would you want to store an error message into a variable that's intended for a data value anyway? Why not just return it as a null?
• SwapnilBhate: Hi Andriy, In question its was asked to just print the value (if present in table) or an error message. I tried it in my SQL 2012 environment and it worked (provided the select query returns only single row TOP 1 can be used.