July 2, 2015 at 11:50 am
I am reviewing some code we have inherited (riddled with multiple nested cursors) and in the process of re-writing some of the code. I came across this and it has me puzzled.
As I understand it, if you declare a variable and then try to re-declare a variable of the same name an error is generated. If I do this inside a While loop this does not seem to be the case. What ever is assigned is kept and just added to (in the case of a table variable)
I understand things are in scope for the batch currently running but I would expect an error to return (example 1 and 2)
Could anyone tell me why this is please? I came across this post (http://www.sqlservercentral.com/Forums/Topic829935-338-1.aspx) but it never arrived at the Why 😉
--Table var declaration in loop
SET NOCOUNT ON
DECLARE @looper INT = 0
WHILE @looper <= 10
BEGIN
DECLARE @ATable TABLE ( somenumber INT )
INSERT INTO @ATable
( somenumber )
VALUES ( @looper )
SET @looper = @looper + 1
END
SELECT *
FROM @ATable AS at
GO
-- Var declaration no loop
SET NOCOUNT ON
DECLARE @looper INT = 0
DECLARE @BTable TABLE ( somenumber INT )
INSERT INTO @BTable
( somenumber )
VALUES ( 1 )
DECLARE @Btable TABLE ( somenumber INT )
INSERT INTO @BTable
( somenumber )
VALUES ( 2 )
SELECT *
FROM @BTable AS bt
GO
-- Var declaration in a loop
DECLARE @looper INT = 0
WHILE @looper < = 5
BEGIN
DECLARE @boo INT
SET @boo = @looper
SET @looper = @looper + 1
END
PRINT @boo
July 2, 2015 at 12:40 pm
Don't know why, but I would never leave it that way for any amount of money. It would just confuse someone somewhere and be a potential source of concern.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 2, 2015 at 2:04 pm
Ditto that. Getting stripped out along with the nested cursors.
July 2, 2015 at 2:46 pm
This is due to a funny behavior of DECLARE statements that has been leveraged for QotDs here several times, if my memory serves correctly.
Variable declarations occur at compile time, not run time, and thus occur once regardless of how many times the batch containing the declaration is run. The most common exploitation of that for funny QotDs has been placing a variable declaration in a branch that will not execute at all, and then in the branch that does execute, assigning a value to the variable.
Counter to most people's intuitions, that code will run without error, even though the branch containing the declaration does not get executed. An example of that is this:
IF 1=0
BEGIN
DECLARE @looper INT
END
ELSE
BEGIN
SET @looper=10
PRINT @looper
END
I haven't found any good official documentation of this behavior (another reason people have liked it for tricky QotDs), but it's been noted occasionally. The best way to think of it is that the DECLARE statements aren't really "executed" like other statements, although there's some illusion of this because the parser forces you to declare variables earlier in the code than the variables are used. That misleadingly can cause people to think that DECLARE statements follow normal control-of-flow like other statements.
As the code above shows, they do not. The variable declarations occur prior to execution of any code, and the parser just makes sure there are not multiple declare statements for the same variable in the batch, and that the declaration of a variable occurs before any references to that variable.
The loop exploits that, since the parser only sees one declaration. It has no idea how many times that block will end up getting executed, and since DECLARE statements aren't really "executed" like other statements anyway, it doesn't matter.
Also check out Martin Smith's answer to a similar question at http://stackoverflow.com/questions/14963742/sql-server-variable-loop-vs-duplicate. He makes the same basic point with some more supporting queries.
Cheers!
July 2, 2015 at 2:50 pm
Perfect thanks. I will sleep tonight:w00t: it's been bugging me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply