Variable Declaration

Question

Post reply

Variable Declaration

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

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

jlennartz SSCommitted Points: 1574 More actions · Answer 1

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

+1

paulcauchon SSC Veteran Points: 245 More actions · Answer 2

Toreador (9/19/2013)
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 😉

Very true...I hope code like this would end up in real life about as often as this code would 😉 :

select 'Hello World!'

Perhaps even less often-- I can't think of a single instance where a code block wrapped in if 1=0 would be a useful design element.

paulcauchon SSC Veteran Points: 245 More actions · Answer 3

SQL 2005 Necromancers (9/19/2013)
It no worky in SQL2005! Foul!

+1

You all are absolutely correct. I should have specified that the code was written and tested in 2008R2, and would not demonstrate the intended behavior in versions before SQL2008, but that would just have taken the fun out of it! Say high to Server 2003 and Windows XP for me!

The beauty of this is to understand the intricacies of how SQL Server consumes our code, ultimately to get closer to ~~master~~ing SQL Server.

PS-- J/K about the necromancers part...I know a lot of times we have absolutely no say in the environments we inherit or wind up coding in. I'm sure if we had our pick, we'd all be coding in SQL2012 on machines with exabyte drives, a billion gigs of ram, and eighty thousand core processors. I know I would 😀

Happy coding everyone!

Paul

sateesh.lingozi SSC Rookie Points: 38 More actions · Answer 4

Hi,

One small question it might be bit odd to ask it.. jst wanted to understand

As per the question the variables are declared in the "if" Block, logically speaking the execution does not reach the block as the condition fails.. but how come the declaration is done and else statements are executed perfectly.

Can't i try the same declaration in else and check if the query posted compiles properly(actually it does not).

Thanks in advance

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

paulcauchon (9/19/2013)
Perhaps even less often-- I can't think of a single instance where a code block wrapped in if 1=0 would be a useful design element.

I've used stuff like that to get around the SET FMTONLY tricks that SSIS pulls.

Imagine a stored proc that takes 5 minutes to execute to return several million rows.

Create your temp table

If 1 = 0

insert 1 row of test data

else

run query that puts results in temp table

then return the contents of the test table.

It allows you to develop code a lot faster because you don't have to wait 5 minutes every time you close the source for SQL to analyze it

--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]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

mtassin (9/20/2013)
paulcauchon (9/19/2013)
Perhaps even less often-- I can't think of a single instance where a code block wrapped in if 1=0 would be a useful design element.
I've used stuff like that to get around the SET FMTONLY tricks that SSIS pulls.
Imagine a stored proc that takes 5 minutes to execute to return several million rows.
Create your temp table
If 1 = 0
insert 1 row of test data
else
run query that puts results in temp table
then return the contents of the test table.
It allows you to develop code a lot faster because you don't have to wait 5 minutes every time you close the source for SQL to analyze it

I have also used similar tricks when dealing with SSIS. It is also helpful with SSRS on occasion.

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

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

Revenant

SSC-Forever

Points: 42467

September 20, 2013 at 11:43 am

#1651993

Good question - thanks, Paul!

AK_K Right there with Babe Points: 791 More actions · Answer 8

This works in Sql 2008 and above but fails in SQL 2005.

[font="Times New Roman"]Anil Kubireddi[/font]

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

Koen Verbeeck (9/19/2013)
Very similar to the question a few days ago, but a bit harder.
Really nice question, thanks.

Ditto

colt Old Hand Points: 317 More actions · Answer 10

I'm running SQL Server 2000.

This code throws an error like the one for version 2005.

Marcia J SSCertifiable Points: 5649 More actions · Answer 11

Leifton (9/19/2013)
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

I tried it in 2008 R2 and got the expected results.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 12

If I found a DECLARE nested in an IF like that in real life, there would be some high velocity pork chops in the future of the Develper that wrote it. 🙂

Excellent question, though!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlnaive SSCoach Points: 17437 More actions · Answer 13

Thanks to some QOTD asked some time back, I was able to answer it correctly without giving second thought. The errors with the script in SQL 2005 were interesting.

Though it's bad practice to do it this way, good attempt to clear the point. 🙂

Paul_Wheeler SSC Journeyman Points: 76 More actions · Answer 14

Good Question! From now on I should learn to think in 208R2 and newer (at least for QOTD). :hehe:

Good (if embarrassing) reminder about the parsing vs. execution flow.

jfgoude SSCrazy Points: 2586 More actions · Answer 15

Luis Cazares (9/19/2013)
There's not an "official" version. ...
The question might have needed to specify it should be used on 2088+ but the syntax shows it clearly ....

I'm too old for waiting on this one version 2088+ sigh will never this sure !

+1 as it was the same as previous other day