Variable Declaration

Question

Post reply

Variable Declaration

paulcauchon

SSC Veteran

Points: 245
More actions
September 18, 2013 at 10:55 pm

#298933

Comments posted to this topic are about the item Variable Declaration
September 19, 2013 at 1:57 am

This was removed by the editor as SPAM
September 19, 2013 at 2:53 am

This was removed by the editor as SPAM

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

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

palotaiarpad SSCertifiable Points: 5617 More actions · Answer 1

Good question! Learned something. Parsing does not set initial values! 🙂

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

Very similar to the question a few days ago, but a bit harder.

Really nice question, thanks.

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

bclavier SSC Enthusiast Points: 137 More actions · Answer 3

SQL server 2005

Msg 139, Level 15, State 1, Line 0

Cannot assign a default value to a local variable.

Msg 137, Level 15, State 2, Line 7

Must declare the scalar variable "@word".

Msg 137, Level 15, State 2, Line 11

Must declare the scalar variable "@word".

Msg 137, Level 15, State 1, Line 12

Must declare the scalar variable "@word".

Msg 137, Level 15, State 2, Line 13

Must declare the scalar variable "@word".

paul s-306273 SSChampion Points: 10730 More actions · Answer 4

bclavier (9/19/2013)
SQL server 2005
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 7
Must declare the scalar variable "@word".
Msg 137, Level 15, State 2, Line 11
Must declare the scalar variable "@word".
Msg 137, Level 15, State 1, Line 12
Must declare the scalar variable "@word".
Msg 137, Level 15, State 2, Line 13
Must declare the scalar variable "@word".

+1

Keld Laursen (SEGES) SSC Eights! Points: 843 More actions · Answer 5

Interesting.

I failed because I didn't remember the discussion about the QOTD from the other day. There it were made abundantly clear that the declaration and assignment were made in two passes. Thus, the variable exists, but the apple isn't put into it.

Well, can't win them all...

Keld Laursen (SEGES) SSC Eights! Points: 843 More actions · Answer 6

Stewart "Arturius" Campbell (9/19/2013)
good question, thanks.
bclavier (9/19/2013)
SQL server 2005
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
<snip>
this will be expected pre SQL2008. for those, you cannot assign a value to a variable in line. therefore, the variable will not be declared.
to make this work in pre SQL2008, make the following change:
if 1=0
begin
declare @word varchar(100)
select @word = 'apple'
declare @table table(id int)
<snip>

Which will sort of eliminate the "declaration and assignment in same line" pitfall that were exposed in the QOTD.

In this case, I think it would have been prudent to mention the version restrictions in the question.

SQLDoubleG Hall of Fame Points: 3100 More actions · Answer 7

Thanks for the question.

Interesting the logical execution of

declare @word varchar(100) = 'apple'

in two phases.

Toreador SSChampion Points: 11365 More actions · Answer 8

I'd never have got this right if it weren't for the discussion following the other recent variable declaration question!

Interesting to know how SQLServer behaves - but hopefully such shoddy code wouldn't get written in real life 😉

SQL Swerver Hall of Fame Points: 3074 More actions · Answer 9

This one plays with my mind.

On the one hand it gives a demonstration of how not to write code and explains why by giving an useful insight into the way that SQL Server parses code.

On the other hand I find the question annoying because it is asking you how SQL Server responds to such bad code, except that I don't write code like that so it's not a failing on my part that I got the answer wrong. Grrrrr!

Dscheypie SSCommitted Points: 1545 More actions · Answer 10

palotaiarpad (9/19/2013)
Good question! Learned something. Parsing does not set initial values! 🙂

+1

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

Gary Stacey SSC Eights! Points: 999 More actions · Answer 11

Stewart "Arturius" Campbell (9/19/2013)
Keld Laursen (VFL) (9/19/2013)
I think it would have been prudent to mention the version restrictions in the question.
if memory serves, QotD only relates to the current mainstream versions of SQL Server at the time of the question being posted. That would, currently, be SQL2008, SQL2008R2 and SQL2012.
SQL2005 is no longer in mainstream support (despite the number of customers still using it)

While that's true, I think it would have been useful to mention a version in this case, given the difference in behaviours between versions.

Leifton SSC Veteran Points: 298 More actions · Answer 12

Not sure how everyone is getting this right. The script fails (as i suspected) in SQL 2005, 2K8 and 2K12. Since the first block is not run due to the false condition, how do the variables get declared for the second block?

Curious