I have a cursor. Within my loop I select
a single column and assign the result to a variable. When the select
returns an empty rowset, nothing is assigned to the variable. In particular, the variable retains its current value which, in my case, is the value it held on the last iteration of the loop.
The following code illustrates this.
declare @testA int;
declare @testB int;
set @testA = 1;
set @testB = 2;
select @testA = id from (values (3)) as testtable(id) where testtable.id = 4 ; -- empty rowset
select @testB = (select id from (values (5)) as testtable(id) where testtable.id = 6); -- empty rowset
select @testA as testA, @testB as testB;
@testA
retained its value (1) while @testB
was updated to null
.
The only difference between the two assignment attempts was that in case B a subquery was used (via the addition of the parentheses and an additional select
keyword).
This says to me a subquery (which always returns a single value) represents an empty rowset as null
in that return value, but attempting to select a single column from an empty rowset does not return null
. Rather, it kind of says "I have no value, so there is nothing to assign".
My understanding of the definition of null
is that it represents an "undefined" value.
If I am saying "select 'id' where these conditions are met" and there are no 'id' values meeting such conditions, does this not imply the 'id' value I want is undefined?
Best Answer
A subquery used in a context that expects a single, scalar value needs to return a single, scalar value. Such contexts are when used as an expression or with operators that expect a single, scalar value. When no scalar value exists, it returns
NULL
for precisely the reason you thought: it means "unknown". Think of a subquery in these contexts as being a replacement for a scalar UDF. It will always return a single something (T-SQL does not allow for a return type ofvoid
). So, what you have is effectively:select @testB = dbo.Function(6);
assuming that "dbo.Function" is defined as:More examples:
and:
and:
I wouldn't think of an empty result set in terms of "I have no value, so there is nothing to assign". Instead, think of it in terms of: an assignment will be performed for every row returned, and no rows returned means that there is no action to take. It might be a subtle difference, but the focus really needs to be on "rows = actions" more so than "returned value is something that can be assigned".
This distinction is not so subtle when:
You are setting a value like you are showing for
select @testA = id from ...
. The assignment will happen per each and every row returned. And the final value of the variable will be the last row returned. That can be predictable if you use anORDER BY
, or you can live life on the edge and go for unpredictable results by not using anORDER BY
. For example:Another way to look at this is that you are using a result set as a loop:
ResultSet.GetNextRow()
returns false (due to no rows in the result set), then the loop is never entered and the assignment never happens.In fact, this is how string concatenation works when done via result set (please note: as Martin Smith pointed out in this Stack Overflow answer, string concatenation done in this manner is not guaranteed to produce the expected result, even if most of the time it does; however, that does not take away from this method proving my point here):
I even made use of this behavior to generate a complex T-SQL script:
Uppercase All String Columns (and in a Single Query!)