Geek asks and answers

SELECT @Var and SET @Var behavior in T-SQL when working with NULL

Every day I learn something new, it seems :) Maybe someone, please explain to me the rationale for the following code behavior:

DECLARE @A INT

SET @A = 15
SET @A = (SELECT ValueThatDoesntExist FROM dbo.MyTable WHERE MyColumn = 'notfound')

SELECT @A
-- Rsultset is NULL

SET @A = 15
SELECT @A = ValueThatDoesntExist FROM dbo.MyTable WHERE MyColumn = 'notfound'

SELECT @A
-- Resultset is 15

From what I see, SET changes the value of the variable if the result set is NULL, and SELECT is not. Is this regular ANSI behavior or is it specific to T-SQL?

Of course, if I do SELECT @A = NULL, the assignment is done correctly.

+5
source share
4 answers

The first version sets A to the query result:

SET @A = (SELECT ValueThatDoesntExist FROM dbo.MyTable WHERE MyColumn = 'notfound')

Mostly selectin in a scalar context, and if it does not find a string, it evaluates to null.

The second version sets A for each row in the result set:

SELECT @A = ValueThatDoesntExist FROM dbo.MyTable WHERE MyColumn = 'notfound'

, A . :

declare @a int

select  @a = i
from    (
        select  1
        union all select 2
        union all select 3
        ) as SubQueryAlias(i)
order by
        i

select  @a

3 @a. 3, .

+4

, . .

.

+2

SELECT . :

SELECT @A = ...

, . , , .

, , .

+2
SET @A = (SELECT ValueThatDoesntExist 
                       FROM dbo.MyTable WHERE MyColumn = 'notfound')

NULL

SELECT @A = ValueThatDoesntExist FROM dbo.MyTable WHERE MyColumn = 'notfound'

+1

More articles:


All Articles