THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Yet another gotcha: variables' scopes do not end where they should.

Be careful: unlike most other languages, T-SQL does not limit variables' scope to the block where the variable has been defined. For example, the following snippet compiles and runs:

 -- @to is not in scope yet
-- the line below would not compile
--SET @to = @to + 3 ;

IF DATEPART(weekday, GETDATE()) = 3
    
BEGIN ;
    
-- the scope of this DECLARE does not end when the block ends
        
DECLARE @from INT ,
            
@to INT ;
        
SELECT  @from = 5 ,
                
@to = 7 ;
        
SELECT  Number
        
FROM    data.Numbers
        
WHERE   Number BETWEEN @from AND @to ;
    
-- both @from and @to are defined beyond this point
    
END
ELSE
    BEGIN
;
    
-- Big surprise:
    -- @from and @to are still in scope, this compiles and runs
        
SET @to = @to + 3 ;
        
SELECT  Number
        
FROM    data.Numbers
        
WHERE   Number BETWEEN @from AND @to ;
    
END ;  
 

This is counter-intuitive for most developers that worked with other languages. IMO this is one of those features we would probably be better off without. What do you think?

Published Wednesday, January 25, 2012 5:09 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Tokarski said:

It's feature or bug? Maybe its not itentional?

January 26, 2012 12:59 AM
 

Eugene Lobanov said:

It's clear for me. We have a batch and we have variables in the batch =) Everything is clear.

What about usability, it's other question...

Alexander are you from Russia?

January 26, 2012 2:11 AM
 

Dave Ballantyne said:

Hi Alex , same issue with table vars caused me confusion a while back

declare @x int =0

while(@x <=10) begin

  declare @tv table ( col1 integer)

  insert into @tv values(@x)

  Select * from @tv

  select @x+=1

end

January 26, 2012 4:23 AM
 

Peter said:

Declares are only evaluated once but if they contain a default value, the default value part is evaluated everytime the code is run.

declare @x int = 0;

while(@x < 5) begin

 declare @y int = 0;

 select @x+=1, @y+=1;

 select @x as [@X], @y as [@Y];

end

January 26, 2012 5:07 AM
 

Dave Morrison said:

IMHO this looks like a classic example of .net / software developer trying to force that way of thinking into sql, which it's really not designed for.

Don't get me wrong, I started out on that side of the fence and probably wrote tons of sql that way, because that's how I was used to structuring code

If you're writing a lot / all your sql code like this with loads of begin, end, if etc in my opinion you're not really using SQL for what it was designed for.

It's designed to run in batch processing

This is just my viewpoint however so I'm open to debate

January 26, 2012 11:20 AM
 

Alexander Kuznetsov said:

@Adam: I think this is intentional.

@Eugene: yes, I am of Russian origin, but I live in the US.

@Dave Ballantyne: thank you for the example! One more potential confusion to avoid ;(

@Peter: good example!

@Dave Morrison: Probably yes, originally "It's designed to run in batch processing", but I think that currently lots of people are using T-SQL to develop stored procedures.

I do need a layer of stored procedures to isolate my client code from the details of database implementation. However, because of gotchas like this I do not really want to implement anything complex in it.

Does it make sens to you?

January 26, 2012 12:10 PM
 

Julian Kuiters said:

The easiest way to remember that variables are scoped to the batch is to always declare all your variables first.

-- declare variables

DECLARE @from INT ,

       @to INT ;

IF DATEPART(weekday, GETDATE()) = 3

   BEGIN ;

       SELECT  @from = 5 ,

               @to = 7 ;

       SELECT  Number

       FROM    data.Numbers

       WHERE   Number BETWEEN @from AND @to ;

   -- both @from and @to are defined beyond this point

   END

ELSE

   BEGIN ;

   -- Big surprise:

   -- @from and @to are still in scope, this compiles and runs

       SET @to = @to + 3 ;

       SELECT  Number

       FROM    data.Numbers

       WHERE   Number BETWEEN @from AND @to ;

   END ;  

January 26, 2012 4:52 PM
 

AaronBertrand said:

Alex, I don't think that being armed with this knowledge it should prevent you from doing anything complex in your stored procedures. Like Julian said, if you declare your variables up front instead of inside conditionals, it becomes a lot less complex.

January 26, 2012 4:57 PM
 

Dave Morrison said:

Alex, not to be argumentative but the fact that "lots of people" are doing it doesn't really make any more correct?

In my mind its about where different type of logic lives.

I totally agree that you should create an abstraction layer between the database and applications (I prefer TVF's personally) but if you're doing a load of BEGIN, END, IF type stuff in this abstraction layer you run the risk of, amongst other things, killing your performance. Take a look at this post by Rob Farley (http://msmvps.com/blogs/robfarley/archive/2009/12/05/dangers-of-begin-and-end.aspx)

Languages such as VB.net & C# etc are far better suited for this "line by line" type logic and IMHO this is where it should be done.

It comes to do my original point, applying a C# etc developers mindset to TSQL

January 27, 2012 6:22 AM
 

GrumpyOldDBA said:

Sorry don't see this as a gotcha as it's always been this way. Is it maybe because T-SQL doesn't compile or how the statemenst are interpreted prior to execution ? Sorry it's so many years since I did that type of computer science and I've been working in SQL for 16 odd years so have never given it any thought - bit like speaking another language - it'd be nice if Italian was the same as English but it isn't and you just have to remember.

January 27, 2012 8:18 AM
 

AlexK said:

@Aaron,

Although knowing this nice little feature will not prevent me from doing anything complex in my stored procedures, it will discourage me a little bit ;)

@Dave Morrison:

I am not sure Rob's post is completely relevant. He is essentially arguing that inline TVFs are faster than multi-statement ones. No argument here, but is there serious performance degradation when we use IF-THEN-ELSE in stored procedures?

I would be happy to learn if that is the case.

@GrumpyOldDBA:

I think we newbies know better than you old hands what is a gotcha and what is not ;). If something feels wrong/inconsistent to a newbie, it can be a gotcha. This is why many shops rotate people between projects - new team members are much better at noticing inconsistencies and discrepancies. Gotchas like @@SERVERNAME and DB_NAME() do not make the product better, they just make the learning curve steeper.

We just got a team member back after several months of absence, and she is making lots of useful suggestions on how we could improve our product.

January 31, 2012 4:27 PM
 

Aristos said:

Agree this is confusing, since BEGIN / END blocks do not behave in the same way as more OOP style if/for loop scope. As I understand it, variables have batch scope, which is defined by the use of the GO statement. BEGIN .. END is  used for control flow statement scope (e.g. IF scope / rollback transaction scope)

Ignoring the IF statement from your example and replacing with a GO statement in between, we can see that variable scope has the "expected" effect:

DECLARE @from INT ,

   @to INT ;

SELECT  @from = 1;

SELECT  @to = 2 ;

BEGIN ;

-- the scope of this DECLARE does not end when the block ends

   SELECT  PhoneNumberTypeID

   FROM    Person.PhoneNumberType

   WHERE   PhoneNumberTypeID BETWEEN @from AND @to ;

END

-- both @from and @to are defined beyond this point...

GO

--but not after GO

BEGIN ;

-- statement will error if variables are not decalred again here!

   DECLARE @from INT,

       @to INT ;

   SELECT  @from = 1;

   SELECT  @to = 3;

   SELECT  PhoneNumberTypeID

   FROM    Person.PhoneNumberType

   WHERE   PhoneNumberTypeID BETWEEN @from AND @to ;

END ;  

References

http://msdn.microsoft.com/en-gb/library/ms188927.aspx

http://msdn.microsoft.com/en-gb/library/ms190487.aspx

http://msdn.microsoft.com/en-us/library/ms188037(v=sql.110).aspx

July 7, 2014 12:02 PM

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement