Hugo Kornelis (8/26/2010)
da-zero (8/26/2010)
I do not really understand how the scope of the type variable works. It seems you can define it over and over again, without an error being thrown?and
martin.whitton (8/26/2010)
It's interesting that a variable can be declared multiple times if it's within a "while" loop (provided that it's only declared once in each iteration).When I first read the question I thought that declaring @a within the loop would cause an error on the second iteration.
The variable is actually declared once. Declarations are not relly executable statements. They are processed when a batch is parsed, not when it it executed. The declaration has to be positioned before the use in "left to right / top to bottom" order, not in execution order. That is why this works, even though the code path that containst the declaration is never executed.
IF 1 = 2
BEGIN;
PRINT 'Skip this';
DECLARE @a int;
END;
ELSE
BEGIN;
SET @a = 1;
END;
SELECT @a;
And this works as well, even though the part where the declaration sits is executed AFTER the assignment:
GOTO Label2;
Label1:
DECLARE @b-2 int;
SELECT @b-2;
GOTO Label3;
Label2:
SET @b-2 = 3;
GOTO Label1;
Label3:
go
(Note that I do not endorse using GOTO in T-SQL code, nor deliberately writing spaghetti code for any other purposes than illustrating the difference between order of parsing and order of execution)
Kind of new for me - thanks - great explanation and examples