john.arnott (2/11/2010)

---

<rant on> I can't imagine that 85% of respondants actualy knew the result of the SELECT
where the condition is false would not be assigned to the variable. </rant>

What difference does it make? At the end of the day, answering a good QotD correctly only gives you two things:
1. A better understanding of SQL / SQL Server.
2. An increase to your SSC points.
. . .
So, those people who just do the copy-and-paste are actually cheating themselves out of better knowledge for a few measley points. Let them.

Rune Bivrin (2/12/2010)

---

I got it right because I knew it, but I understand why that might not be obvious, and the behaviour is slightly iffy.

If you do

SELECT @var = MAX(object_id) 
FROM sys.objects
WHERE 1=0

you'll get NULL assigned, but

SELECT @var = object_id
FROM sys.objects
WHERE 1=0

will retain the previous value of @var.

It's really a source for unexpected behaviour when a WHERE-clause is a little wrong, and relying on it when coding isn't really fair to whoever will maintain your code.

SELECT max(object_id) from sys.objects where 1=0

SELECT @var=max(object_id) from sys.objects where 1=0

SELECT object_id from sys.objects where 1=0

SELECT @var = object_id from sys.objects where 1=0