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

No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts