declarations

Question

Post reply

declarations

TomThomson

SSC Guru

Points: 104773
More actions
August 18, 2014 at 11:46 pm

#310172

Comments posted to this topic are about the item declarations
Tom
August 19, 2014 at 2:32 am

This was removed by the editor as SPAM

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

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

Hany Helmy SSChampion Points: 13921 More actions · Answer 1

Easy, thx 🙂

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

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

Easy one if you remember Toms explanation of the previous QotD:

DECLARE @i INT = 0;

equals

DECLARE @i INT;

SET @i = 0;

Thanks for the question!

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

Zagyg SSCrazy Points: 2091 More actions · Answer 3

Koen Verbeeck (8/19/2014)
Easy one if you remember Toms explanation of the previous QotD:
DECLARE @i INT = 0;
equals
DECLARE @i INT;
SET @i = 0;
Thanks for the question!

Agreed.

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

andy.brown (8/19/2014)
Koen Verbeeck (8/19/2014)
Easy one if you remember Toms explanation of the previous QotD:
DECLARE @i INT = 0;
equals
DECLARE @i INT;
SET @i = 0;
Thanks for the question!
Agreed.

Thanks for the question, Tom. I genuinely appreciate the syntax that allows declaration and initial assignment, but this is proof that they're really separate. I hope I don't see code like this in production.

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

SqlMel SSCrazy Points: 2901 More actions · Answer 5

Good question, Tom. Thanks!

This is a good example of how things can go wrong when using this syntax.

---------------
Mel. 😎

Thomas Abraham SSChampion Points: 10761 More actions · Answer 6

Thanks for the question Tom. It shows quite clearly why serious consideration should be given to avoid the combo declare/set syntax.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

LearnModeOn SSC Enthusiast Points: 154 More actions · Answer 7

LearnModeOn

SSC Enthusiast

Points: 154

August 19, 2014 at 7:00 am

#1739191

Just learned something new!

Thx

select this!!

stephen.long.1 SSCrazy Points: 2582 More actions · Answer 8

Nice question, Tom. Thanks.

I like this syntax, if it is used properly (and not in the way documented here). I work in SQL Server and in Sybase ASE, which does not have this syntax, which makes me appreciate it even more. It's a shame that BOL doesn't document this very well (unlike the rest of BOL, which is, of course, VERY complete;-)).

dhober Ten Centuries Points: 1084 More actions · Answer 9

Unless you're running 2005 or older, in which case you'd have a nice little syntax error happening 😉

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 10

Thank you, Tom, very interesting one.

As the old saying goes "everything has pros and cons", so any one see the "pros" side of it?

(I am kind of feeling now, its one of the stupid question from me)

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

TomThomson SSC Guru Points: 104773 More actions · Answer 11

Raghavendra Mudugal (8/19/2014)
Thank you, Tom, very interesting one.
As the old saying goes "everything has pros and cons", so any one see the "pros" side of it?
(I am kind of feeling now, its one of the stupid question from me)

The pro is of course that if you put all the declarations combined with assignment in a non-repeated part of the stored procedure, trigger, or batch the assignments get executed exactly once, so the net effect is much the same as declaration with initialisation to a chosen value and this can quite useful for making the code easier to read (it reduces clutter).

Something that is often claimed as a plus for this is that it eliminates the possibility of uninitialised variables, but that's pure nonsense. It doesn't have the effect that adding initialisation to declarations in some other languages, specifically languages which have variable declarations without initialisation, since (scalar) variable declarations in T-SQL initialise the variable to NULL anyway: there no such thing as an uninitialised variable in T-SQL; thus the claim that it eliminates the uninitialised variable problem from T-SQL (which I've seen rather too often) is nonsense, don't be fooled into thinking that that imaginary effect is a plus of allowing combined declaration and assignment syntax as an abbreviation for a declaration plus a set statement.

edit: typos

Tom

Ken SSCoach Points: 17191 More actions · Answer 12

Ken

SSCoach

Points: 17191

August 19, 2014 at 12:57 pm

#1739358

Nice and easy one. Thanks Tom.

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 13

TomThomson (8/19/2014)
The pro is of course that if you put all the declarations combined with assignment in a non-repeated part of the stored procedure, trigger, or batch the assignments get executed exactly once, so the net effect is much the same as declaration with initialisation to a chosen value and this can quite useful for making the code easier to read (it reduces clutter).

Thank you, Tom. When I came to know the in-line assignment types, I said, great as I did in VB.NET, now I can do in SQL too, probably, in future versions there will also be an option to mention ByVal or ByRef (like stuff) in procedure/function level....and slowing sql scripting will be taken over by OOPS concepts.

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.