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

Search

Most popular posts
in last 180 days

Categories

My Projects

Archives

Stuff


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)
   2:   
   3:  INSERT INTO @t VALUES (1)
   4:  INSERT INTO @t VALUES (2)
   5:  INSERT INTO @t VALUES (3)
   6:  INSERT INTO @t VALUES (4)
   7:   
   8:  DECLARE CurTest CURSOR FOR SELECT Id FROM @t OPEN CurTest DECLARE @Id int
   9:   
  10:  FETCH NEXT FROM CurTest INTO @Id    
  11:  WHILE @@FETCH_STATUS = 0
  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
  19:   
  20:  END
  21:   
  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 |

Feedback

# 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:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70664

# 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.