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

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

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?

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

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

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

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 ;  

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.

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

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.

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