Gotcha #121008: SELECT @Variable vs SET @Variable in SQL-Server

Introduction

Due to the nature of the work I've been doing lately, it's been a while since I've had to cut much T-SQL above and beyond standard CRUD operations; but whilst fixing a stored procedure the other day, I was bitten by this little gotcha.

The Scenario

Imagine that we have the following table in our database:

Now take a look at the following T-SQL snippet:

DECLARE @MyVar INT

SELECT @MyVar = Id
FROM dbo.MyTable
WHERE Description = 'Foo'

IF @MyVar IS NOT NULL
 PRINT @MyVar
ELSE
 PRINT '[Null]'
 
SELECT @MyVar = Id
FROM dbo.MyTable
WHERE Description = 'Boo'

IF @MyVar IS NOT NULL
 PRINT @MyVar
ELSE
 PRINT '[Null]'

Now what would you expect the output to be? Maybe:

1
[Null]

Wrong! The actual output is:

1
1

So why is this? You will note that the second SELECT statement has been written to deliberately return 0 rows from the database. Now when SELECTing into a variable which already contains data; using a query which does not return any rows, the existing value of the variable is left intact and not overwritten.

In this trivial example, the result is hardly critical, but you can imagine that if a stored procedure (or script or whatever) pivoted around testing @MyVar for NULL, your execution flow could very easily go off on an unexpected tangent.

If we make the following changes to our code, the result will be as we expected:

DECLARE @MyVar INT

SET @MyVar = ( SELECT Id
    FROM dbo.MyTable
    WHERE Description = 'Foo')

IF @MyVar IS NOT NULL
 PRINT @MyVar
ELSE
 PRINT '[Null]'

SET @MyVar = ( SELECT Id
    FROM dbo.MyTable
    WHERE Description = 'Boo')

IF @MyVar IS NOT NULL
 PRINT @MyVar
ELSE
 PRINT '[Null]'

When using the SET statement, the value of @MyVar will always be overwritten. If the query does not return any data, the value @MyVar will be cleared.

Conclusion

When putting data into a variable which you may later want to test for NULL, it is safer to use the SET statement, rather than the SELECT statement.