November 6, 2007 at 6:03 am
Hi all!
I was writing a small segment of code
DECLARE @Tmp nvarchar(50)
SET @Tmp = 'testdata'
SELECT @Tmp = [Code] FROM LookupTable WHERE [Code] = @Tmp
SELECT @Tmp
@Tmp always contains the `testdata` value, even when `testdata` doesnt appear in my LookupTable. I expected @Tmp to reset to Null if the code wasnt found.
Changing this to
DECLARE @Tmp nvarchar(50)
DECLARE @tmp2 nvarchar(50)
SET @Tmp = 'testdata'
SELECT @tmp2 = [Code] FROM LookupTable WHERE [Code] = @Tmp
SELECT @tmp2
works as designed, value if there, null if not
I dont supose anyone can shed any explination on this? To me @Tmp should set itself to whatever the results of my query is. Unless theres some cunning set based processing here that overrides the ability for the variable to get a value set. Im presuming theres a propper reason!
Many thanks
martin
November 6, 2007 at 6:26 am
DECLARE @Tmp nvarchar(50)
DECLARE @tmp2 nvarchar(50)
SET @Tmp = 'testdata'
SELECT @tmp2 = Code FROM LookupTable WHERE Code = @Tmp
SELECT @tmp2
works as designed, value if there, null if not
As said, if the query returns 0 rows, no assignment happens. You can verify by altering you code to be
DECLARE @Tmp nvarchar(50)
DECLARE @tmp2 nvarchar(50)
SET @Tmp = 'testdata'
set @tmp2 = 'Untouched!'
SELECT @tmp2 = Code FROM LookupTable WHERE Code = @Tmp
SELECT @tmp2
What if LookupTable has two matching rows? You'll find the behaviour is undefined / not guaranteed but you will get one o the two values from the table (try it - the "order by" clause in this case also doesn't help).
The alternate syntax, which is
set @tmp2 = (select Code from LookupTable where Code = @Tmp)
will complain bitterly if you have >1 row returned. It also behaves as you expect by setting @tmp2 to NULL if there is no matching row. This is because you're doing two operations rather than one - namely you're executing the select statement to retrieve a recordset. The recordset is rejected if it has >1 row. If it has no rows then the result is effectively NULL. If it has exactly one row then you receive the appropriate column's value.
The select code you initially presented does the select and assignment in one operation on a row by row basis - if there's no row then no assignment will occur (even to NULL). If there is a row, or many rows, then an assignment, or many assignments, will occur with the last assignment being the one that takes effect.
Hope that helps clarify things! 🙂
November 7, 2007 at 4:45 am
much clearer
thanks guys 🙂
martin!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply