Bite my bytes

What I learn by day I blog at night - A blog from Microsoft Consultant working from Ljubljana, Slovenia

  Home :: Contact :: Syndication  
  999 Posts :: 7691 Comments :: 235 Trackbacks


Most popular posts
in last 180 days


My Projects



Copyright © by David Vidmar
Contact me!
LinkedIn Profile

Check out the SQL code below.

It creates temp table @t, fill number 1 to 4 and creates a cursor to step over them. In every step I print out the @Id and if @Id is less or equal assign it to another variable @AnotherId. Then it prints out that variable.

   1:  DECLARE @t TABLE (Id int)
   3:  INSERT INTO @t VALUES (1)
   4:  INSERT INTO @t VALUES (2)
   5:  INSERT INTO @t VALUES (3)
   6:  INSERT INTO @t VALUES (4)
  10:  FETCH NEXT FROM CurTest INTO @Id    
  12:  BEGIN
  13:      PRINT 'Id        = ' + ISNULL(CAST(@Id AS varchar), 'NULL')
  14:      DECLARE @AnotherId int
  15:      IF @Id <= 2 
  16:          SET @AnotherId = @Id 
  17:      PRINT 'AnotherId = ' + ISNULL(CAST(@AnotherId AS varchar), 'NULL')
  18:      FETCH NEXT FROM CurTest INTO @Id
  20:  END
  22:  CLOSE CurTest
  23:  DEALLOCATE CurTest

 Now it gets tricky. You would expect to see something like this as a result:

   1:  Id        = 1
   2:  AnotherId = 1
   3:  Id        = 2
   4:  AnotherId = 2
   5:  Id        = 3
   6:  AnotherId = NULL
   7:  Id        = 4
   8:  AnotherId = NULL

But instead you get

   1:  Id        = 1
   2:  AnotherId = 1
   3:  Id        = 2
   4:  AnotherId = 2
   5:  Id        = 3
   6:  AnotherId = 2
   7:  Id        = 4
   8:  AnotherId = 2

Whoa! It behaves exactly the same as if @AnotherId would be declared out of the loop!

I was not aware of that and I created a nice little bug that way. I checked with Mladen and he was surprised to see what is going on. Any idea? The SQL Server documentation states that “scope of a variable is the batch in which it is declare”. Is it that simple?

Posted on Thursday, August 17, 2006 2:45 PM | Filed under: Developement |


# re: Is this SQL Server bug? 8/17/2006 2:59 PM Peter Larsson
No, it is not a bug.

When ID is 3 or more, the SET operation for AnotherID does not happen. Thus AnotherID still has old value.

# re: Is this SQL Server bug? 8/17/2006 3:08 PM David
What about the scope? The variable is define inside the loop so the it's value should be discarder and the end of the loop.

# re: Is this SQL Server bug? 8/17/2006 3:31 PM Mladen
This is also discussed here:

# re: Is this SQL Server bug? 8/17/2006 3:34 PM David
Tnx. I hope it helps. :)

# re: Is this SQL Server bug? 8/17/2006 3:47 PM David
My current explanation:
- the value of @AnotherId is not defined after declaration
- the memory reservation hives @AnotherId the same memory address it had in previous loop and this way it looks like the value was retained

The proof would be a situation where @AnotherId would have a value different that in previous loop but still not NULL.

# re: Is this SQL Server bug? 8/25/2006 2:32 PM Enric
I think as David. Each time the program creates the var, it is allocated in the same memory position. If nobody initializes it, it has the same value that it had before.

Some languages initialize variables when create them but other leave this task to the programmer.

# re: Is this SQL Server bug? 9/1/2006 3:58 PM ML
The *scope* is the current session.

Think in sets, not procedurally. This is 4GL. ;)

Comments have been closed on this topic.