set @variable then select @variable = no rows

here’s a strange one that came up the other day. if you have the following sql statement:

DECLARE @AppId INT

SET @AppId = 1

SELECT @AppId = 2

WHERE 0 = 1 --i.e. a condition that forces an empty recordset to be returned

SELECT @AppId

since @AppId was selected from a table with no rows (WHERE 0 = 1), i was expecting it to be NULL after this executes… but it turns out that the variable maintains its value from the SET call , so it’s 1. just an unexpected side effect to be aware of--