T-SQL Oddities

Question

Post reply

T-SQL Oddities

deepforest

SSCommitted

Points: 1780
More actions
March 22, 2010 at 1:12 pm

#139518

from BOL:
Declaring a Transact-SQL Variable
The DECLARE statement initializes a Transact-SQL variable by:
* Assigning a name. The name must have a single @ as the first character.
...
Nothing wrong with that, except maybe "a single @ as the first character". It is hard to imagine to squeeze more single anything as the first character.
But, did you know that the following will compile just fine and it'll work as well?
declare @ datetime, @@ int, @@@ varchar(16),@$ int, @$#$@@ int
select @=getdate(),
@@=1,
@@@='funny variable',
@$=2,
@$#$@@=3
select @,@@,@@@,@$,@$#$@@
As long as the first character is a 'single' @, any combo of {'$','#'} will do.
I'm saving my alphanums for later 😉

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

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

WayneS SSC Guru Points: 95409 More actions · Answer 1

deepforest (3/22/2010)
But, did you know that the following will compile just fine and it'll work as well?

Why, yes. 😛

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

...did you know that the following will compile just fine and it'll work as well?...

Yes

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Yes. Nice tidbit though to point out.

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

Yes! There was a really funny Question of the Day about names.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Garadin One Orange Chip Points: 29613 More actions · Answer 5

Fine, I'll bite. I didn't!

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

The Dixie Flatline (3/22/2010)
Yes! There was a really funny Question of the Day about names.

Which one was that? I don't recall it.

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 7

Jason, I just searched back through T-SQL QotDs looking for the string "Name" and couldn't find it. 🙁

I distinctly remember some of the odd things from it though. For example a blank is a valid table name.

select top 10 name

into [ ]

from sys.databases

select * from [ ]

drop table [ ]

Try putting a period, an apostrophe, etc. within the brackets as a table name. They all work.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

The Dixie Flatline (3/22/2010)
Jason, I just searched back through T-SQL QotDs looking for the string "Name" and couldn't find it. 🙁
I distinctly remember some of the odd things from it though. For example a blank is a valid table name.
select top 10 name
into [ ]
from sys.databases
select * from [ ]
drop table [ ]
Yup - I have seen the odditites such as that. If importing from excel, those happen frequently.:-D
Try putting a period, an apostrophe, etc. within the brackets as a table name. They all work.

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

matzer SSCrazy Eights Points: 9727 More actions · Answer 9

Note that variable names @, @@ or names starting with @@ are deprecated in SQL 2008. See http://msdn.microsoft.com/en-us/library/ms143729.aspx:

Do not use @ or @@ or names that begin with @@ as identifiers.

Peter

deepforest SSCommitted Points: 1780 More actions · Answer 10

Garadin (3/22/2010)
Fine, I'll bite. I didn't!

somebody is kranky today...

Garadin One Orange Chip Points: 29613 More actions · Answer 11

Heh, no, you mistook me. I wasn't cranky at all. I was joking around with them all being buzzkills with the 'already knowing it' and letting you know that I didn't, and thus this post was educational to someone at least :hehe:. I think my wording could have been better though.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

deepforest SSCommitted Points: 1780 More actions · Answer 12

deepforest

SSCommitted

Points: 1780

March 23, 2010 at 7:49 am

#1138222

just kidding, just kidding..;-)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Garadin (3/23/2010)
Heh, no, you mistook me. I wasn't cranky at all. I was joking around with them all being buzzkills with the 'already knowing it' and letting you know that I didn't, and thus this post was educational to someone at least :hehe:. I think my wording could have been better though.

Nope - didn't take it as cranky and took the post just as you explained.

Just trying to be a buzzkill still 😉

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

Paul White SSC Guru Points: 150467 More actions · Answer 14

deepforest (3/22/2010)
But, did you know...

Yes.

One of my favourite QODs was the one that executed a procedure named GO.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi