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.
It isn't really iffy, and it's the behaviour which you would naturally expect if you looked at the select - you just have to consider what the raw select (without the local variable assignment) would return.
SELECT max(object_id) from sys.objects where 1=0
returns NULL because applying max to an empty set produces NULL. So SELECT @var=max(object_id) from sys.objects where 1=0
sets @var to NULL. On the other hand, SELECT object_id from sys.objects where 1=0
doesn't return anything, so SELECT @var = object_id from sys.objects where 1=0
doesn't have any value (not even null) to assign to @var and so it doesn't assign anything.
Tom