A SQL Server tidbit that I never knew before today. This was a test on SQL 2000. I’d be interest to know if you get the same result on something more current.
[Update: still just like this on SQL 2008]
What do you suppose this returns?
[crayon-5df68511198fa983722283 inline="true" ]
declare @x varchar(90)
select @x = 'asfdfs'
-- there is no row in Orders with OrderID = 4550768,
-- but assume the table name and column names are valid.
select @x = OrderName from Orders where OrderID = 4550768
A – NULL
B – asfdfs
C – an error message
D – OrderName
Correct Answer: B.
I would have expected A. It turns out that I had a variable assignment in a loop, and from time to time, it wasn’t getting assigned a new value. The problem was on rows where the select statement returns no rows. In that case, my varible still had the value from its previous iteration.