T-SQL Syntax

  • jack a hummer

    SSC Veteran

    Points: 220

    Comments posted to this topic are about the item T-SQL Syntax

  • Jamsheer

    Ten Centuries

    Points: 1136

    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

    SSC Guru

    Points: 281243

    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[/url]
    Learn Extended Events

  • rVadim

    Hall of Fame

    Points: 3969

    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 ?

    --Vadim R.

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    Great question, but way too difficult for a Friday 🙂

    Didn't even think about RECEIVE.

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

  • MissTippsInOz

    Hall of Fame

    Points: 3158

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

  • jack a hummer

    SSC Veteran

    Points: 220

    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

    SSC-Insane

    Points: 21872

    Maybe, you miss the UPDATE clause:

    declare @mykey int

    update top 1 mytable set

    @mykey = mykey

    where status = 0

    print @mykey

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    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

    SSC Guru

    Points: 64675

    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/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • SQL Surfer '66

    SSCertifiable

    Points: 5152

    Only took a shot at this one (and missed). Today I don't have the time to search the documentation, but mostly because this was IMHO a bit to complex question.

  • Gazareth

    One Orange Chip

    Points: 27737

    Guessed & got it right - although I only answered so I could see the actual answer!

    Like the question for making me think about it, the right/wrong was irrelevant really.

    Would definitely like to see it expanded to an article if possible.

    Cheers

  • This was removed by the editor as SPAM

  • (Bob Brown)

    SSCrazy

    Points: 2705

    I agree. Not a good QotD. This should bring lots of discussion though and is very educational.

  • Nunnery

    Ten Centuries

    Points: 1289

    Good Question - When in doubt, the answer is always 'C' 🙂

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

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