Assigning Variables When No Row Exists

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]
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.

Posted in MS SQL, Transact SQL | Leave a comment

Comments are closed.