Point to consider when using SELECT over SET in SQL Server

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.

[sourcecode language=”sql”]

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
[/sourcecode]

Here is the screen capture of the output of above query:

SET_SELECT

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.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

1 thought on “Point to consider when using SELECT over SET in SQL Server”

Comments are closed.