SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL Syntax


T-SQL Syntax

Author
Message
jack a hummer
jack a hummer
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 390
Comments posted to this topic are about the item T-SQL Syntax



Jamsheer
Jamsheer
Right there with Babe
Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)

Group: General Forum Members
Points: 747 Visits: 237
Good question, very difficult to answer.

But
"An Input argument to a Procedure or Function"
can be split into
"An Input argument to a Procedure" and
"An Input argument to a Function".

So we could get the total of 9. isn't it?
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)SSC Guru (67K reputation)

Group: General Forum Members
Points: 67845 Visits: 18570
Tough question - thanks



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

rVadim
rVadim
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1437 Visits: 2308
Indeed, very difficult and controversial question.
Counted Function and Procedure as different constructs but missed EXEC and RECEIVE.
Oh well, as soon as we learn something…

Also, what about UPDATE/INSERT/DELETE @tablevar ?
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63850 Visits: 13298
Great question, but way too difficult for a Friday :-)
Didn't even think about RECEIVE.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
MissTippsInOz
MissTippsInOz
SSC Eights!
SSC Eights! (811 reputation)SSC Eights! (811 reputation)SSC Eights! (811 reputation)SSC Eights! (811 reputation)SSC Eights! (811 reputation)SSC Eights! (811 reputation)SSC Eights! (811 reputation)SSC Eights! (811 reputation)

Group: General Forum Members
Points: 811 Visits: 597
rVadim (8/16/2012)

....missed EXEC and RECEIVE.


Also, what about UPDATE/INSERT/DELETE @tablevar ?


I missed RECEIVE too. I have to say not a lot of work has gone on in our office this afternoon while we debated why DML against a table variable shouldn't be valid!!

Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
jack a hummer
jack a hummer
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 390
My newsletter has not even rolled in yet, but thought I would say Hi! Glad to see people found it interesting. It just popped into my head while researching BOL one day.

Jamsheer - An input argument in either case just seems to be the same thing to me.

rVadim - I think you did find another variation, e.g. INSERT @tablevar VALUES (1, 2). Congratulations.

Hope everyone had fun.



Carlo Romagnano
Carlo Romagnano
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7584 Visits: 3400
Maybe, you miss the UPDATE clause:

declare @mykey int

update top 1 mytable set
@mykey = mykey
where status = 0

print @mykey



I run on tuttopodismo
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10482 Visits: 5157
Carlo Romagnano (8/17/2012)
Maybe, you miss the UPDATE clause:

declare @mykey int

update top 1 mytable set
@mykey = mykey
where status = 0

print @mykey



Hi, this is covered in the question itself: ...says "without using SET nor SELECT"
IgorMi

Igor Micev,
My blog: www.igormicev.com
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19011 Visits: 12426
I appreciate the amount of research that went into this question and I think it has a good educational value, but I don't think a QotD is the right form for this education - at least not a QotD in this specific form. That is because the education here is just in reading the answer, not in trying to find it yourself. It is simply nigh on impossible to find a complete list, because that would require one to know EVERYTHING in the T-SQL syntax, or to read BOL entirely. Without putting in that effort (which is far too much to be worth it), you can only get this right by having a lucky guess.

Not to mock the author, but to prove my point that the T-SQL language is simply too complex to get away with ANY question of the "how many" kind, here are three constructs that are missing in the answer explanation:

1. UPDATE tablename SET @variablename = columnname = expression;
2. EXEC sp_executesql (this one is debatable; one could argue that this is covered by the "Output argument to a Procedure" in the answer, or you could argue that the dynamic naturre of sp_executesql makes this a distinct construct);
3. The default value assignment in a procedure definition: CREATE PROC MyProc @MyVar int = 12 AS ...

And I wouldn't be surprised at all if other people can extend this list even more.

EDIT: Posts by Carlo and IgorMi came in while I was writing, hence the duplication of the UPDATE consntruct. I read the QotD as excepting the SELECT and SET statements, not the UPDATE statement which includes a SET clause. (And I think the author had this in mind, given that he describes the OUTPUT INTO clause as "could count this 4 times" - if the SET clause is forbidden, the OUTPUT INTO can only be counted 3 times, because an UPDATE without SET is invalid)


Because I wanted to see the answers and weigh in on the discussion, I decided to take an "educated guess" at the question. I applied human psychology to eliminate the lowest and highest value from the answer options, then made a random pick from the remaining options. And I got lucky, so I now have another point to brag about to anyone who wants to hear (i.e., nobody).

Again, I do appreciate the effort that has gone in the question and the educational value from the explanation. I just think that this information was more suited for a short article, blog post, or similar. Or for a completely different form of question.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search