Do your T-SQL variables have the right values?

For some of us who are bewildered by even the code we write ourselves, this might be a question we ask ourselves in more circumstances than we’d like to admit, but what I’m talking about here is assigning values to a variable with a SELECT statement, and expecting the value to be null if the query didn’t find anything.  This can be particularly nefarious in cursor loops as I will show.  First, to set up the scenario, run the following script.  It creates two temp tables – one with 10 rows and one with 5:

IF OBJECT_ID('tempdb..#one') IS NOT NULL DROP TABLE #one
IF OBJECT_ID('tempdb..#two') IS NOT NULL DROP TABLE #two
CREATE TABLE #one (number INT IDENTITY (1,1))
CREATE TABLE #two (number INT IDENTITY (1,1))
GO
INSERT #one DEFAULT VALUES
GO 10
INSERT #two DEFAULT VALUES
GO 5
SELECT * FROM #one
SELECT * FROM #two

number
-----------
1
2
3
4
5
6
7
8
9
10 (10 row(s) affected) number
-----------
1
2
3
4
5 (5 row(s) affected)

Now we will use a cursor loop to go through the first (10-record) table and look for matching values in the second table – we should obviously only find 5 matching values – and display our findings.  This is what it *should* look like:

DECLARE @CR CURSOR, @Number INT, @Found INT
SET @CR = CURSOR LOCAL FAST_FORWARD FOR
SELECT number FROM #one
OPEN @CR
FETCH NEXT FROM @CR INTO @Number
WHILE @@FETCH_STATUS = 0
BEGIN
   RAISERROR('Looking for number %i...',0,0,@Number)
   SET @Found =(SELECT number FROM #two WHERE number = @Number)
   IF @Found IS NOT NULL
       RAISERROR('Found: %i',0,0,@Found)
   FETCH NEXT FROM @CR INTO @Number
END

Looking for number 1...
Found: 1
Looking for number 2...
Found: 2
Looking for number 3...
Found: 3
Looking for number 4...
Found: 4
Looking for number 5...
Found: 5
Looking for number 6...
Looking for number 7...
Looking for number 8...
Looking for number 9...
Looking for number 10...

Of course, it only found the five (by the way, please don’t take this as a recommendation to use cursors for this type of thing – I’m illustrating an unrelated point here…) How about the following, though – there is a subtle difference – do you see it right away?

DECLARE @CR CURSOR, @Number int, @Found int
SET @CR = CURSOR LOCAL FAST_FORWARD FOR
SELECT number FROM #one
OPEN @CR
FETCH NEXT FROM @CR INTO @Number
WHILE @@FETCH_STATUS = 0
BEGIN
   RAISERROR('Looking for number %i...',0,0,@Number)
   SELECT @Found = number FROM #two WHERE number = @Number
   IF @Found IS NOT NULL
       RAISERROR('Found: %i',0,0,@Found)
   FETCH NEXT FROM @CR INTO @Number
END

Looking for number 1...
Found: 1
Looking for number 2...
Found: 2
Looking for number 3...
Found: 3
Looking for number 4...
Found: 4
Looking for number 5...
Found: 5
Looking for number 6...
Found: 5
Looking for number 7...
Found: 5
Looking for number 8...
Found: 5
Looking for number 9...
Found: 5
Looking for number 10...
Found: 5

So “SELECT @Var =” and “SET @Var =” are the same thing, right? Well, it appears not.  Using the SELECT method, the variable is not resetting to NULL.  There is a simple explanation for this.  The SET method is setting the variable to the output of a query – that query did not return anything which is a missing value – the very definition of NULL – but the SELECT method is changing the variable as part of the query.  Since the query returns no rows, the variable assignment never takes place so the value of the variable does not change.

You may want the variable to change to NULL and you may not – so choose your method accordingly, but I have seen code that uses the SELECT method followed by a test for NULL clearly expecting the variable to be nulled if no record is found.

Exercise:  What happens if you use an aggregate like MAX() with the SELECT method – do you know what it will do before you try it?  Try it and see if you’re right (hint:  aggregate queries always return at least one row).

Be careful out there – the devil is in the details.