Declare Variable

Question

Declare Variable

Viewing 8 posts - 31 through 38 (of 38 total)

You must be logged in to reply to this topic. Login to reply

TomThomson SSC Guru Points: 104773 More actions · Answer 1

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

Revenant SSC-Forever Points: 42467 More actions · Answer 2

Revenant

SSC-Forever

Points: 42467

September 16, 2013 at 12:38 pm

#1650574

Hugo and Tom are the best explainers, as usual.

Hany Helmy SSChampion Points: 13921 More actions · Answer 3

Revenant (9/16/2013)
Hugo and Tom are the best explainers, as usual.

+1

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

MaBear Old Hand Points: 355 More actions · Answer 4

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!

sanjay.barik SSC Rookie Points: 49 More actions · Answer 5

I didn't get the accurate reason of this query snippet.

marlon.seton SSCrazy Points: 2623 More actions · Answer 6

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.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

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

jfgoude SSCrazy Points: 2586 More actions · Answer 8

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

Declare Variable

Cookies on SQLServerCentral