1

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
3
  • 5
    Why the dynamic SQL? Why not somethign simple like SELECT @place_holder = id FROM #test1 WHERE name = @color; ? Nov 28, 2016 at 13:33
  • 1
    @ypercubeᵀᴹ - 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! Nov 28, 2016 at 13:34
  • 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).
    – Andriy M
    Dec 5, 2016 at 6:39

2 Answers 2

3

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
1
  • 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! Nov 28, 2016 at 13:33
0

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
2
  • 1
    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?
    – Andriy M
    Dec 5, 2016 at 6:55
  • 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. Dec 15, 2016 at 4:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.