• 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