SQL Server select variable where no results

Question

Can I set variables if the query returns no results?

DECLARE @Output AS VARCHAR(MAX);

SELECT @Output = 'Old';
SELECT @Output = 'New' WHERE 0 = 1;

PRINT @Output;

Output value is: Old

Expected value is: NULL

Answer 1

DECLARE @Output AS VARCHAR(MAX);
SELECT @Output = 'Old';
SELECT @Output = 'New' WHERE 0 = 1;
PRINT @Output;

You get 'Old' cause the variable already has this value, and it will not update the value since in the WHERE clause you use the condition 0=1, which will return FALSE and the value in the variable won't change.

WHERE 0 = 1 It will be False

WHERE 0 <> 1 It will be True

It's just similar to IF 0=1 THEN UpdateMyVar

So in your case the value will always 'Old', it won't return 'New' or NULL either.

I don't know what are you trying to do really, but if you want to return NULL then

DECLARE @Output AS VARCHAR(MAX);
SELECT @Output = 'Old';
SELECT @Output = NULL WHERE 0 <> 1; --If 0<> 1 then update my var and set NULL else leave it as it is
PRINT @Output;