Point to consider when using SELECT over SET in SQL Server
Few days back I got one stored procedure from one of the developer who told me that SP was working fine till now but today it is not functioning well. I have tried to look at the issue and logically it seems perfect as per the business logic we had and there is no reason for it to not to work properly.
After having one more review of TSQL code inside the SP, I have found that SELECT statement were used to assign the value to variable rather then SET statement and that was causing an issue. Personally I highly prefer to use SET over the SELECT if I have to assign value to variable.
Let me show you practically what kind of issue can happen if we use SELECT to assign value to variable.
DECLARE @Var VARCHAR(10) SET @Var='1' PRINT @Var SET @Var='' PRINT @Var SET @Var='2' PRINT @Var PRINT '--SET Completed, SELECT Begin--' DECLARE @Var1 VARCHAR(10) SELECT @Var1='1' WHERE 1=1 PRINT @Var1 SELECT @Var1='2' WHERE 1=0 PRINT @Var1 SELECT @Var1='3' WHERE 1=1 PRINT @Var1
Here is the screen capture of the output of above query:
After seeing the result, can you assume what wrong has happened?
If you observer first three SET statement. You will find proper result. We have assigned value 1 then blank and then 2 to variable and those you can see printed in screen capture.
Now observer three SELECT statement. We have assigned “1” value in variable in first SELECT statement and when we print, we can see “1” printed for first SELECT in screen capture.
In second SELECT statement, we have assigned “2” but we have WHERE condition 1=0 which means there won’t be any value return by this query, not even NULL and now if you print variable, you can see value “1” is printed because SELECT is taking previously assigned value which is wrong. Now we have to make exercise to clean up previously assigned value if we want to use SELECT so better avoid SELECT and use SET.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.