Declare Variable

Question

Post reply

Declare Variable

Samith C

Mr or Mrs. 500

Points: 519
More actions
September 14, 2013 at 11:10 am

#298775

Comments posted to this topic are about the item Declare Variable
[font="Verdana"] There is no Wrong time to do a Right thing 🙂 [/font]
September 16, 2013 at 1:18 am

This was removed by the editor as SPAM
September 16, 2013 at 1:25 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 40 total)

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

Hany Helmy SSChampion Points: 13921 More actions · Answer 1

Nice & easy.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

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.

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

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 3

And again, nice and easy wins the race.

Hope this helps...

Ford Fairlane
Rock and Roll Detective

Carlo Romagnano SSC-Insane Points: 22662 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.

+1

Basic question.

🙂

sqlnaive SSCoach Points: 17437 More actions · Answer 5

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.

Definitley +1. Thanks for giving the extra information Koen. 🙂

In between, good question.

Carlo Romagnano SSC-Insane Points: 22662 More actions · Answer 6

In the following batch @j-2 is declared once, but initialized at every loop:

declare @i int

set @i = 2

while @i < 10

begin

declare @j-2 int = @i

print @i

print @j-2

set @i += 1

end

Umer Akram SSC-Insane Points: 22209 More actions · Answer 7

Umer Akram

SSC-Insane

Points: 22209

September 16, 2013 at 1:34 am

#1650329

nice basic question

Carlo Romagnano SSC-Insane Points: 22662 More actions · Answer 8

Stewart "Arturius" Campbell (9/16/2013)
Carlo Romagnano (9/16/2013)
In the following batch @j-2 is declared once, but initialized at every loop:
declare @i int
set @i = 2
while @i < 10
begin
declare @j-2 int = @i
print @i
print @j-2
set @i += 1
end
remember, within the confines of a while loop, each iteration is a new scope.

No, the scope is to the end of the batch, here, you can reference @j-2 out of the loop.

declare @i int

set @i = 2

while @i < 10

begin

declare @j-2 int = @i

print @i

print @j-2

set @i += 1

end

print @j-2

[/quote]

psingla Hall of Fame Points: 3840 More actions · Answer 9

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.

Hopefully that's why we can not declare same variable multiple times in different scopes.Like we can do in c,C++,Java,SSIS etc

As per me it's not properly handled by the parser.

Either variable declaration within a "if block" should not be allowed otherwise variable scope should be limited to "if block" only.

Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/

SQLDoubleG Hall of Fame Points: 3100 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.

+1, the declare statement is not ignored as is parsed before the execution starts. If not,

print @j-2

would always fail, as wouldn't be declared.

Good question, though

Dscheypie SSCommitted Points: 1545 More actions · Answer 11

Carlo Romagnano (9/16/2013)
No, the scope is to the end of the batch, here, you can reference @j-2 out of the loop.
declare @i int
set @i = 2
while @i < 10
begin
declare @j-2 int = @i
print @i
print @j-2
set @i += 1
end
print @j-2

[/quote]

Good point, Carlo, but the loop is interpreted later!

Look at this:

declare @i int

set @i = 2

set @j-2 = 1

while @i < 10

begin

declare @j-2 int = @i

print @i

print @j-2

set @i += 1

end

print @j-2

This returns an error message:

Must declare the scalar variable "@j".

This means in my opinion:

The script is interpreted before the execution and variables get declared then, EXCEPT for constructs like the loops which are in there own scope and therefore interpreted later.

Best regards

Jens-Peter 🙂

________________________________________________________
If you set out to do something, something else must be done first.

Carlo Romagnano SSC-Insane Points: 22662 More actions · Answer 12

Dscheypie (9/16/2013)
Carlo Romagnano (9/16/2013)
No, the scope is to the end of the batch, here, you can reference @j-2 out of the loop.
declare @i int
set @i = 2
while @i < 10
begin
declare @j-2 int = @i
print @i
print @j-2
set @i += 1
end
print @j-2

Good point, Carlo, but the loop is interpreted later!

Look at this:

declare @i int

set @i = 2

set @j-2 = 1

while @i < 10

begin

declare @j-2 int = @i

print @i

print @j-2

set @i += 1

end

print @j-2

This returns an error message:
Must declare the scalar variable "@j".
This means in my opinion:
The script is interpreted before the execution and variables get declared then, EXCEPT for constructs like the loops which are in there own scope and therefore interpreted later.
Best regards
Jens-Peter 🙂

The scope is TO THE END of the batch and not before the declaration.