September 16, 2013 at 11:42 am
mtassin (9/16/2013)
Good... so rather than googling myself to death trying to come up with the correct term.I believe it's because of the IF/THEN that we see this.
SQL Processes both branches of the IF/THEN statement during parsing/etc and then during execution it determines which plan to use. I'm totally guessing here, but it would appear that variable declarations are considered part of the if/then block not a result of execution of it. So the IF/THEN is actually part of the creation of the variable, and such, when the IF/THEN finishes we have the variable.
It would explain why this generates an error, essentially the DECLARE statements are part of the IF/THEN block and the declares become available after it completes.
I think this is the best explanation available.
A similar issue was listed here[/url] (explanation is wrong, but corrected in the comments).
It's not exactly the same behavior, but there's an anecdotal comment about "block scope" that applies to this situation. When the parser enters the IF/THEN (Or Loop construct), the body is only created once. It looks like the IF/THEN construct is treated the same way, evaluating all available paths first - including variable declarations.
Or, to point-grub, "Nice easy back-to-basics question".
September 16, 2013 at 12:11 pm
Jeff.MSSqlSage (9/16/2013)
mtassin (9/16/2013)
Good... so rather than googling myself to death trying to come up with the correct term.I believe it's because of the IF/THEN that we see this.
SQL Processes both branches of the IF/THEN statement during parsing/etc and then during execution it determines which plan to use. I'm totally guessing here, but it would appear that variable declarations are considered part of the if/then block not a result of execution of it. So the IF/THEN is actually part of the creation of the variable, and such, when the IF/THEN finishes we have the variable.
It would explain why this generates an error, essentially the DECLARE statements are part of the IF/THEN block and the declares become available after it completes.
I think this is the best explanation available.
Pity it's incorrect, then.
If it were correct, both batches in the following code would produce an error, since in each the variable is used before the IF/THEN which contains the declaration finishes.
if 0 = 1
begin
declare @j-2 int
end
else
begin
set @j-2 = 3
end
select @j-2;
go
if 1=1
begin
declare @j-2 int
end
else set @j-2 = 2;
select @j-2;
The first batch selects 3, and the second batch selects NULL. No errors.
Also, the assignment part of a declaration is executed at run time, not at parse time, so it is executed in an if compound only if the declaration is in the TRUE branch, as you can see from
if 0=1
begin
declare @j-2 int = 3
end
select @j-2
which selects NULL, not 3.
Tom
September 16, 2013 at 12:17 pm
Steve Jones - SSC Editor (9/16/2013)
If someone has a good explanation that makes sense, I'm happy to change it.
The batch is processed in two phases.
Phase one is parsing. This works top to bottom and ignores all control-flow. That's why, if you declare the same variable in a THEN and ELSE, it will raise an error for declaring it twice, even though always exactly one will execute. And that's why, if you declare a variable in a loop that runs multiple time, it will not raise an error. The parser simply reads the text top to bottom, and the only requirement is that references to the variable are "below" the declaration.
Phase two is execution. This phase respects control-flow, but not variable declaration.
I don't know if there are any references for this, and I don't have the time to look for them now.
EDIT: Forgot to add that while this may be interesting to know, bast practice is to put all declarations at the start of a code block, before any executable statements. (Mixing the two in the form of assignment on declaration is okay. Remember that "DECLARE @i int = 0;" is actually just shorthand for "DECLARE @i int; SET @i = 0;" to understand how it behaves).
September 16, 2013 at 12:33 pm
Hugo Kornelis (9/16/2013)
Steve Jones - SSC Editor (9/16/2013)
If someone has a good explanation that makes sense, I'm happy to change it.The batch is processed in two phases.
Phase one is parsing. This works top to bottom and ignores all control-flow. That's why, if you declare the same variable in a THEN and ELSE, it will raise an error for declaring it twice, even though always exactly one will execute. And that's why, if you declare a variable in a loop that runs multiple time, it will not raise an error. The parser simply reads the text top to bottom, and the only requirement is that references to the variable are "below" the declaration.
Phase two is execution. This phase respects control-flow, but not variable declaration.
I don't know if there are any references for this, and I don't have the time to look for them now.
EDIT: Forgot to add that while this may be interesting to know, bast practice is to put all declarations at the start of a code block, before any executable statements. (Mixing the two in the form of assignment on declaration is okay. Remember that "DECLARE @i int = 0;" is actually just shorthand for "DECLARE @i int; SET @i = 0;" to understand how it behaves).
That's technically identical to the explanation in my first comment above, but is a much better way of saying it. Steve, how about using this text of Hugo's to replace the explanation (except for the incorrect bit about initialisation that he tacked on as an afterthought; the variable being declared is not in scope in the initialisation expression, but would be if the initialisation was just the shorthand Hugo says it is; declare @x int; set @x = isnull(@x,3) is valid t-sql, but declare @x int = isnull(@x,3) is not.
Tom
September 16, 2013 at 12:38 pm
Hugo and Tom are the best explainers, as usual.
September 16, 2013 at 12:51 pm
Revenant (9/16/2013)
Hugo and Tom are the best explainers, as usual.
+1
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
September 16, 2013 at 1:08 pm
L' Eomot Inversé (9/16/2013)
Koen Verbeeck (9/15/2013)
The question itself is great, however the explanation is lacking.There's no reference, and the explanation itself is incorrect.
There's no variable that is "reset".
The DECLARE is not ignored, it is interpreted by the parser/optimizer/interpreter/"whatever relevant part of the process" as if was at the start of the batch, instead of inside a IF-ELSE construct.
It is a good question and the explanation is indeed incorect. However, so is your correction: if the declaration were treated as if it were at the start of the batch, its scope would start at the beginning of the batch; but it doesn't; the variable is in scope at every point in the batch's text after the declaration, and at no point in or before the declaration. You can see this by putting "set @j-2=9" anywhere in the batch before the declaration and watching the error, which demonstrates that the variable is out of scope before the declaration, or - in versions of SQL Server that allow declarations to include initialisation - make the declaration read "declare @j-2 int = coalesce(@j,9)" which generates the same error, demonstrating that the variables scope begins after the delare statement, not inside it.
Thanks for the explanation!
September 16, 2013 at 11:07 pm
I didn't get the accurate reason of this query snippet.
September 19, 2013 at 3:15 am
Hany Helmy (9/15/2013)
Nice & easy.
It was only easy for me because there was a question covering the same concept a little while ago.
September 20, 2013 at 12:15 pm
Koen Verbeeck (9/15/2013)
The question itself is great, however the explanation is lacking.There's no reference, and the explanation itself is incorrect.
There's no variable that is "reset".
The DECLARE is not ignored, it is interpreted by the parser/optimizer/interpreter/"whatever relevant part of the process" as if was at the start of the batch, instead of inside a IF-ELSE construct.
+1
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 10, 2013 at 7:05 am
seemed to understand it well BEFORE all these explanations !
one thing is sure begin always by declaring variables, never play with declare in flow of code,
keep simple : do simple
+1
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply