Local Variables

Local Variables

I would like to point one interesting property how values are assigned to local variables

SELECT *

FROM   sales.store

WHERE  SalesPersonID = 99999999

This will not return any rows.

Now we will assign value of BusinessEntityID to @id
Lets how its value is affected

1.

DECLARE @i INT

SET @I = 1

SELECT @i = BusinessEntityID

FROM   sales.store

WHERE  SalesPersonID = 99999999

SELECT @i 

Here since the query is not returning any value value of @id is not affected
and its same 1.

2.

DECLARE @i INT

SET @I = 1

SELECT @i = (SELECT BusinessEntityID

             FROM   sales.store

             WHERE  SalesPersonID = 99999999)

SELECT @i

Here its NULL 
why so ?
Its because here value is assigned from sub query and its returning no values
So when subquery returns no values the variable will reset to NULL