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
4 Answers
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;
2 Comments
profimedica
My Where condition is set to false because I need to simulate a query that returns no result. There are side other effects in my context if I change my where condition. So, I need to alter the variable on an empty resultset.
Martin Smith
@profimedica - you can't in the
SELECT if the resultset is empty. You can check @@ROWCOUNT immediately after and do something there.Try this:
DECLARE @Output AS VARCHAR(MAX);
SELECT @Output = 'Old';
SELECT @Output = CASE 0 WHEN 1 THEN 'New' ELSE NULL END
SELECT @Output;
2 Comments
Yogesh Sharma
This doesn't address the op.
profimedica
Your switch case works always on false branch. It is a valid syntax but my code was isolated from a complex procedure where the WHERE condition has multiple conditions. For readability I simplified it in this question.
Sami provides a good explanation of what is going on.
If you want to be sure that a value is assigned, then you need to be sure that the query returns one row for the assignment. One way to do this is to use aggregations:
DECLARE @Output AS VARCHAR(MAX);
SELECT @Output = 'Old';
SELECT @Output = MAX('New') WHERE 0 = 1;
SELECT @Output;
Comments
This is simply how assigning variables in SELECT is implemented in SQL Server. As @SMor said in the comments, use SET instead of SELECT.
DECLARE @Output AS VARCHAR(MAX);
SET @Output = 'Old';
SET @Output = (SELECT 'New' WHERE 0 = 1);
-- this would change the @Output value to NULL
SELECT @Output;
The only problem with SET is that it can assign only one variable, while in SELECT you can assign many variables at the same time.
NULL, it returns no rows. If you aren't passing a value (includingNULL) to the assignment, then the variable won't be updated, no value was passed to assign it.NULLis not the absence of a value, it is an unknown value; which is still a value.@@RowCountafter the secondselectto determine if a row was processed. It will be zero in this case.