Declare Variable

Question

Declare Variable

September 16, 2013 at 6:53 am

This was removed by the editor as SPAM

Viewing 15 posts - 16 through 30 (of 40 total)

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

Richard Warr SSCertifiable Points: 6967 More actions · Answer 1

This isn't a "nice and simple" or "easy one" at all. Although the explanation is wrong the question illustrates a very subtle nuance with the way scripts are run. Logically we'd expect to see an error because the second DECLARE looks like it's never executed. The reason why we don't is not at all obvious.

Erwin Oosterhoorn SSCrazy Points: 2320 More actions · Answer 2

Richard Warr (9/16/2013)
This isn't a "nice and simple" or "easy one" at all. Although the explanation is wrong the question illustrates a very subtle nuance with the way scripts are run. Logically we'd expect to see an error because the second DECLARE looks like it's never executed. The reason why we don't is not at all obvious.

Exactly my view, might be simple if you are aware of this otherwise it's not at all.

Neeraj Prasad Sharma Ten Centuries Points: 1315 More actions · Answer 3

Thanx for the question, its good to refresh memory.:-)

Neeraj Prasad Sharma
Sql Server Tutorials

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

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.

I agree with Koen - good question but the answer is lacking in detail.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

logitestus SSCrazy Points: 2928 More actions · Answer 5

Something to wake me up (outside the morning cup of joe).

Thanks!

kamineni_srinivasarao Valued Member Points: 71 More actions · Answer 6

Hi,

Just wondered..

declare @j-2 statement is inside the if block.

in this scenario sql server have display the error message stating not declared. But how it is ignoring.

Please explain..

Tee Time Hall of Fame Points: 3745 More actions · Answer 7

Tee Time

Hall of Fame

Points: 3745

September 16, 2013 at 6:34 am

#1650396

Thanks!

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 8

Richard Warr (9/16/2013)
This isn't a "nice and simple" or "easy one" at all. Although the explanation is wrong the question illustrates a very subtle nuance with the way scripts are run. Logically we'd expect to see an error because the second DECLARE looks like it's never executed. The reason why we don't is not at all obvious.

It's an easy question because if my memory serves me right, this question has already been asked before. 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 9

There's not reference as the author didn't provide one and I couldn't find one. It's a bit of a hole in the SQL documentation and the way the language works.

If someone has a good explanation that makes sense, I'm happy to change it. As I read it, the "then" portion is never executed, so the variable is not reset. IIRC, the DECLARE essentially resets the value to null each time without an error.

mtassin SSC-Insane Points: 23099 More actions · Answer 10

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.

Thanks for the proper explanation Koen 🙂

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Kevin SSCarpal Tunnel Points: 4962 More actions · Answer 11

The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared.

http://technet.microsoft.com/en-us/library/ms187953(v=sql.105).aspx

TomThomson SSC Guru Points: 104773 More actions · Answer 12

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.

Tom

mtassin SSC-Insane Points: 23099 More actions · Answer 13

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.

DECLARE @i INT = 1

IF @i = 2

DECLARE @j-2 INT = 3

ELSE

DECLARE @j-2 FLOAT = 3.1415

Msg 134, Level 15, State 1, Line 7

The variable name '@j' has already been declared. Variable names must be unique within a query batch or stored procedure.

This seems to happen because until the runtime engine gets to the IF comparison, both paths are considered valid, and when it finishes those resources become available?

In reality this is a big shrug for me, it's one of those things I've seen happen for years... and it's confused developers and just caused me to just ask them to declare all the variables they need at the beginning of their stored procs.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 14

kevin.l.williams (9/16/2013)
The scope of a variable is the range of Transact-SQL statements that can reference the variable. The scope of a variable lasts from the point it is declared until the end of the batch or stored procedure in which it is declared.
http://technet.microsoft.com/en-us/library/ms187953(v=sql.105).aspx

Not sure this is a good explanation. The "then" clause is in scope with the variable, but doesn't cause a parser error. I suspect this would be an executable error, but not sure.