There are a few subtleties when using SELECT to assign a value to a SQL variable. The one we will highlight today is what occurs when the result set of the SELECT is empty.

Given the following SQL, what will be the value of the @Blah variable after this query is ran:

DECLARE @Table TABLE( SomeColumn INT )

DECLARE @Blah INT
SET @Blah = 10

SELECT @Blah = SomeColumn FROM @Table t

SELECT @Blah

Well since the @Table has no data in it yet, it will return an empty result set thus you may be inclined to thing that it will assign a NULL value to the variable.

In reality, it will not assign anything to the variable at all and simply allow the variable to keep any previously assigned value thus the answer is 10.

Now if we were to use SET to assign the value instead of SELECT, we will get the typically assumed behavior of a NULL be assigned to the variable like so:

DECLARE @Table TABLE( SomeColumn INT )

DECLARE @Blah INT
SET @Blah = 10

SET @Blah = ( SELECT SomeColumn FROM @Table t )

SELECT @Blah