TSQL thing I didn't know SET and SELECT

It always surprises me how many characteristics in a programming language I take for granted. The things like short-circuit evaluation, handling of Null values, IF then Else loops, etc. I was recently reading my complimentary book on Defensive Database Programming with SQL Server by Alex Kuznetsov, and its chalked full of things to watch out for in code.

One of the more obvious behaviors that I came across is the slight but subtle difference between Set and Select with regards to assigning a value to a variable. I was always operating under the assumption that if a select retrieved a null value that it would assign it to the variable and life is good. Wrong answer, I must have missed that in books online.

-- some code to demonstrate the behavior

DECLARE @MyCounter INT

-- Display its initial value

SELECT @MyCounter AS 'Display its initial value'

SET @Mycounter = 1

-- Display SET assigned value

SELECT @MyCounter AS 'Display SET assigned value'

SELECT @MyCounter=1 WHERE 1=2

-- Display SELECT assigned value

SELECT @MyCounter AS 'Display SELECT assigned value'

SET @MyCounter =(SELECT 1 WHERE 1=2)

SELECT @MyCounter AS 'Display assigned value using SET and SELECT'

So, this was an interesting find and while I have not come across this in the code base I maintain I am sure I have been close a few times to getting this undesired behavior. I tried this with a NULL value and it worked correctly. The take away is we all need to have a fair amount of unit testing. I find it is best when someone else does the testing because I always overlook something, same as when you write a paper, you read it several times but usually miss something. I have not tried reading code out loud, not sure it would make as much sense.