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.
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:
Wrong! The actual output is:
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
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.
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