Guess who
I’m going to give you three queries, and you have to guess what the output will be before you run them.
Here they are:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
DECLARE @DatabaseName NVARCHAR(256) = N'' SELECT @DatabaseName = d.name FROM sys.databases d WHERE d.name = 'master' AND compatibility_level = 130 ORDER BY d.database_id PRINT @DatabaseName SELECT @DatabaseName = d.name FROM sys.databases d WHERE d.name = 'master' AND compatibility_level = -2147483647 ORDER BY d.database_id PRINT @DatabaseName SELECT @DatabaseName = d.name FROM sys.databases d WHERE d.name = 'tempdb' AND compatibility_level = 130 ORDER BY d.database_id PRINT @DatabaseName |
Go ahead
Feel free to sub in the compatibility level that your databases are actually in before running these on your own server.
I promise, they won’t bite.
But let’s talk about your guesses first!
Will it return…
master
tempdb
Or
master
master
tempdb
You have to pick one before you run it.
Hardweirded
If you guessed two masters, congratulations! You’re smarter than I was a few days ago.
While working on a script, I ran across a weird bug in it. Whatever database was processed last would get processed over and over again until another database was ready.
Picture having 5 databases — on the first run, the loop would hit database 5 and process database 5 several times. On the second loop, it would happen with database 4, and so on, then repeat.
I couldn’t figure out why at first, but then it hit me — the variable wasn’t resetting when it hit a NULL value! It only reset when it hit an actual value. I had to hard code a reset to NULL in between runs.
SET vs. SELECT
I could have avoided some of this trouble by using SET instead. There are some basic differences between the two, but this is an important one.
If I replace my code with this, it correctly assigns the NULL and returns nothing.
|
1 2 3 4 5 6 7 8 9 10 |
DECLARE @DatabaseName NVARCHAR(256) = N'' SET @DatabaseName = (SELECT TOP 1 d.name FROM sys.databases d WHERE d.name = 'master' AND compatibility_level = -2147483647 ORDER BY d.database_id) PRINT @DatabaseName |
Thanks for reading!
35 Comments. Leave new
It’s not that it wasn’t “resetting when it hit a NULL.” The original query (SELECT) returned zero rows. As a result, the variable was never set, as it had no value (not even NULL) to set it to. If a row is returned and d.name is NULL, the variable will be properly set.
So, the issue is the lack of rows returned, not the value.
SET works as expected because when inner select returns no rows, the sub-select expression returns NULL for the value.
I think you’re misreading the post a bit. The variable was set, and not changing when it hit a NULL.
Not at all. It didn’t ‘hit a null.’ SELECT 1 WHEN 1 = 0 doesn’t return a NULL. It simply returns nothing. This is very different than returning a NULL.
I can see that it’s still ambiguous. SELECT @X = 1 WHEN 1 = 0 has no records to assign to @X. There is no NULL to assign. There is no NULL ‘hit.’
SELECT @X = NULL WHEN 1 = 1 will always set @X to NULL. So, the assignment takes place when there is a value, even if it is NULL.
I hate to gang up on your Darling, but I’m with Marc on this one.
When you have a “SELECT @variable = table.column” type of query, the @variable *only* gets touched if *1 or more rows* are returned.
The middle query *does not touch* your @DatabaseName because there are no rows/databases with compatibility_level = -2147483647.
You can test the “1 row returned, but it’s a NULL” scenario by replacing the middle SELECT statement with:
SELECT @DatabaseName = NULL
which gives a result of:
master
tempdb
(note the empty line between master and tempdb, since NULLs aren’t PRINT-able)
Again, I know — see above.
Nothing sounds a lot like NULL to me, but sure.
Unfortunately, nothing is not a NULL at all.
NULL represents a undefined value. NOTHING doesn’t even represent that.
Consider:
SELECT NULL
WHERE 1 = 0
and
SELECT NULL
WHERE 1 = 1
They do different things. One returns a single record consisting of a single column with a NULL value, and the other returns no rows.
In your original code, you made an assignment based on the return of a row. No rows mean no assignment.
This is how SQL has always worked, and I don’t just mean MS SQL.
Yes, I know. I just find this to be overly pedantic. Thanks!
Overly pedantic? What is computer programming and execution if it’s not pedantic?
I accept that you would like
SELECT @X = a.a
FROM a
WHEN 0 = 1
to assign @X = NULL.
But, what about extensions to that?
SELECT @X = ISNULL(a.a, 10)
FROM a
WHERE 0 = 1
What would you like that to assign to @X?
SELECT @X = (SELECT b.b FROM b WHERE b.a = a.a OR (a.a IS NULL and b.a = 50)
FROM a
WHERE 1 = 0
What would like @X to be set to in this case?
@X
But seriously, the whole point of the post is that scalar variable assignment is different between SET and SELECT, and you’ve been harping about one word for two days now with dozens of lines of code.
I have no idea what fruit you expect this exchange to bear.
Don’t be that guy.
Erik,
I apologize if I’m coming across as condescending or if I’m somehow insulted you. But, I really don’t see the need to get personal.
I originally posted because I felt you might have been under a misconception as to the difference between a query returning no rows and one returning NULL values. It was simply a side comment.
When you chose to reply with:
“I think you’re misreading the post a bit. The variable was set, and not changing when it hit a NULL.”
and later,
“Nothing sounds a lot like NULL to me, but sure.”
You turned it into a side conversation about exactly that. You could have simply not responded at any time to end the side conversation.
“Nothing sounds a lot like NULL to me, but sure.” was a joke.
Sort of like “is truncate table DML or DDL?” — is NULL a value or is NULL nothing? And what are we going to do with this NaN?!
Or nothing.
WordPress doesn’t allow NULL comments.
It seems like we’re just crossing streams a bit, here.
Have a good one!
Mark has the right of it. There does not appear to be any issue with NULL value assignment when using SELECT. The statements below test your theory that a NULL value assignment to variable will neither set the variable to NULL nor clear the previous value.
DECLARE @temp nvarchar(10);
SELECT @temp;
SELECT @temp = ‘master’;
SELECT @temp;
SELECT @temp = NULL;
SELECT @temp;
SELECT @temp = ‘master’;
SELECT @temp;
If you run these statements, you will see there is no issue and the variable updates exactly as one would expect. I think Marc explained pretty clearly what was going on and I’d like to add he was rather polite about it, unlike your increasingly defensive and insulting responses. He didn’t deserve that, he was just trying to point out that you misinterpreted your observations, which shouldn’t be such a bad thing on a website that purports to educate.
Mike – make sure to read this part because it’s pretty important:
“the whole point of the post is that scalar variable assignment is different between SET and SELECT”
You’re missing the part about joining to tables. 😀
Hi Brent!
The solution to the observed behavior is simple and elegant, and I’ll certainly be making use of it! The issue I have is with the explanation provided for the observed behavior. Or more specifically, the response when someone questioned the explanation.
“the variable wasn’t resetting when it hit a NULL value”
In our field, this statement means a specific thing and it is not the correct explanation for the observed behavior. It was disappointing to see someone attacked for pointing this out.
Thanks, Mike. I appreciate it.
Rather than a bug, isn’t that actually the expected behavior (whether we are smart enough to guess it or not). The second select statement does not produce a record with name = NULL, but no records, hence no assignment to NULL could be made.
I’ve not run into the situation you describe with variable assignment, but I have been bit in the butt, by changing syntax in this manner
SET @x = (select x from t)
to
Select @x = x from t
I thought the latter was more elegant plus you could make multiple assignments in one selection. I was smart enough to realize that I’d need to be sure that the select statement would only return 1 record, but not smart enough to realize that if my data changed such that my foolproof select statement actually began returning more than one record, the SET @ = (select) syntax would warn me by returning an error while the SELECT @ = syntax would return no error or warning, but merely set the variable to the last value returned given the order it was returned. I’d end up with bad data and not know why. I spent more than a few hours troubleshooting that one before I changed all my code back to SET @ = (select ) syntax.
I don’t think it’s a bug, I just thought it was weird when I ran into it.
Got it.
When you said you ran across a weird bug, I just presumed you were talking about the variable setting behavior.
Ah! No, the bug was in my code. Heh.
The SET syntax is the ANSI/ISO standard. But Microsoft is a little behind the curve on it, because you can do row assignments.. SET (a,b,c, ..) = x, y, z.. ;
You should come out of retirement to crack the whip on Microsoft getting ANSI compliant 🙂
More years ago than I like to think about, and it’s can some consulting when Microsoft was trying to get rid of the old Sybase code and make SQL Server their own. But frankly, these days, I like living on Social Security book royalties and an occasional teaching or consulting job. Do you realize that I’m over 70 years of age? I try to keep track the standards in SQL and anything else, but is a problem goes “my glory days have long since passed, these God damn golden years can kiss my ass!” Maybe I should have done what Graham did and quite a popular novel that will get turned into a movie in the next couple years. Do you think “SQL for smarties – the musical” stands a chance in hell of getting produced 🙁
I don’t know, Joe. I think Broadway is ready for you.
Actually… the SELECT statement does not need to return one record. You can use this to your advantage, and the query analyzer also optimizes based on it. It assigns based on the final record.
SELECT @X = name
FROM sys.databases
ORDER BY name
Generates the same query plan as
SELECT TOP 1 @X = name
FROM sys.databases
ORDER BY name DESC
aka – Why it is a good idea to reset variable values prior re-use. Lots of code stink can occur when you do not reset variables to a default value (like ” or 0) before setting the same variable it to a new value.
setting a variable in a select statement can be a problem not just for this reason, but also because when your query returns multiple rows, it will only run the assignment on one of them (nowadays) in older versions of SQL server, it would run the assignment on each row, so the value you’d get would be the last value in the result set (or no assignment if there are no results, as you point out here) you used to be able to run select @var=@var+’,’+value and get the whole result set (SQL 2000) then you would get a single nondeterministic value from somewhere in the result set. Now I think you get the same value each time, but unless they’ve defined and promised non-changing behavior, it’s always best to set the value to null before, order the query select top 1 if you want your code to consistently return the same value across different versions. Technically there is no guarantee in the syntax that if you set 2 variables to columns from different tables in a join that the values you will get must be in the same row….
Yeah, but it can be useful. Take a look at how Michael Swart uses that to his advantage here.
At least as of SS2012 you can still use SELECT @var = @var_+ ‘,’ to get a comma delimited string. If you have duplicates, you cannot use a DISTINCT or you’ll just get the final value in @var, but you can use a CTE to dedupe before the assignment statement to solve that problem. If you include an order by, you get the comma delimited list in that order. If you set the @var to null first, you can use the following syntax to remove the trailing comma in one statement.
SELECT @var = ISNULL(@var + ‘,’, ” ) + ColName FROM SomeTable.
In fact if that behavior ever changes we’ve got a lot of cleanup work to do in our shop, because that is our preferred way of getting a comma delimited list out of a column in a table.
Ha! I ran into this exact same thing yesterday…
I always use ‘set’, never ran in to this before. Good catch tho, something to look out for.
I like to use 2 variations on your solution (I’ve been caught by the same issue in the past)
/* Opt 1: reset my variables */
DECLARE @DatabaseName NVARCHAR(256) = NULL /* I like to keep it as NULL as not assigned */
SELECT @DatabaseName = d.name
FROM sys.databases d
WHERE d.name = ‘master’
AND compatibility_level = 130
ORDER BY d.database_id
PRINT ISNULL(@DatabaseName, ‘NULL’) /* and then explicitly handle that it could be NULL when outputing it */
/* always reset variables */
SET @DatabaseName = NULL
SELECT @DatabaseName = d.name
FROM sys.databases d
WHERE d.name = ‘master’
AND compatibility_level = -2147483647
ORDER BY d.database_id
PRINT ISNULL(@DatabaseName, ‘NULL’) /* and then explicitly handle that it could be NULL when outputing it */
/* Opt 2: check number of rows returned */
DECLARE @DatabaseName NVARCHAR(256) = NULL /* I like to keep it as NULL as not assigned */
SELECT @DatabaseName = d.name
FROM sys.databases d
WHERE d.name = ‘master’
AND compatibility_level = 130
ORDER BY d.database_id
/* check whether I found a row */
IF @@ROWCOUNT > 0
PRINT ISNULL(@DatabaseName, ‘NULL’) /* note: depending on your query you could have found a row and still set the variable to NULL */
ELSE
PRINT ‘Not found’
SELECT @DatabaseName = d.name
FROM sys.databases d
WHERE d.name = ‘master’
AND compatibility_level = -2147483647
ORDER BY d.database_id
/* check whether I found a row */
IF @@ROWCOUNT > 0
PRINT ISNULL(@DatabaseName, ‘NULL’) /* note: depending on your query you could have found a row and still set the variable to NULL */
ELSE
PRINT ‘Not found’
Oh and a nice ‘trick’, is you can assign a variable in an UPDATE statement:
/* record the previous value*/
DECLARE @PrevName NVARCHAR(50)
UPDATE dbo.Names
SET @PrevName = Name,
Name = ‘Bob’
WHERE ID = 1
/* Also in the select you can manipulate the same variable more than once */
DECLARE @Names TABLE (ID INT, Name1 NVARCHAR(50), Name2 NVARCHAR(50), Name3 NVARCHAR(50))
DECLARE @Name1 NVARCHAR(50), @Name2 NVARCHAR(50), @Name3 NVARCHAR(50)
INSERT INTO @Names (ID, Name1, Name2, Name3)
VALUES (1, ‘a’, ‘a’, ‘a’), (2, ‘b’, ‘b’, ‘b’), (3, ‘c’, ‘c’, ‘c’)
SELECT * FROM @Names
DECLARE @CurrentRowToAssign NVARCHAR(100) = N’Bob|Smith|Jane|’
SELECT
@Name1 = SUBSTRING(@CurrentRowToAssign, 1, CHARINDEX(‘|’, @CurrentRowToAssign, 2) – 1),
@CurrentRowToAssign = SUBSTRING(@CurrentRowToAssign, CHARINDEX(‘|’, @CurrentRowToAssign, 2) + 1, LEN(@CurrentRowToAssign)),
@Name2 = SUBSTRING(@CurrentRowToAssign, 1, CHARINDEX(‘|’, @CurrentRowToAssign, 2) – 1),
@CurrentRowToAssign = SUBSTRING(@CurrentRowToAssign, CHARINDEX(‘|’, @CurrentRowToAssign, 2) + 1, LEN(@CurrentRowToAssign)),
@Name3 = SUBSTRING(@CurrentRowToAssign, 1, CHARINDEX(‘|’, @CurrentRowToAssign, 2) – 1)
UPDATE @Names SET
Name1 = @Name1,
Name2 = @Name2,
Name3 = @Name3
WHERE ID = 2
SELECT * FROM @Names
/* of course, just because you CAN doesn’t mean you SHOULD */