Stored Procedure Creation

  • Robert Eder

    Hall of Fame

    Points: 3326

    Comments posted to this topic are about the item Stored Procedure Creation

  • Carlo Romagnano

    SSC-Insane

    Points: 21876

    That's true! Stack overflow!

  • Hugo Kornelis

    SSC Guru

    Points: 64675

    Aaarggh! I apparently need more coffee.

    I somehow managed to convince myself that the text said "choose 2". And then saw 4 correct answers, so I was very annoyed at the author while choosing the 2 I thought he intended - and then was even more annoyed at myself when I saw why I got it wrong.

    Stupid me.

    Nice question, though. Tricky! Almost really fooled me - that BEGIN and END in the procedure text are misleading. (This is actually one of the reasons why I never use BEGIN END around a stored procedure myself)


    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/

  • This was removed by the editor as SPAM

  • Toreador

    SSChampion

    Points: 11248

    I guessed that it wouldn't execute the procedure ion the 2nd batch, as the parser would detect the recursion and stop it. This seemed to be confirmed when I ran it with "Show Plan" turned on, which showed nothing.

    I should have run Profiler as well, which would have put me right 🙁

  • Hugo Kornelis

    SSC Guru

    Points: 64675

    Toreador (5/14/2015)


    I guessed that it wouldn't execute the procedure ion the 2nd batch, as the parser would detect the recursion and stop it. This seemed to be confirmed when I ran it with "Show Plan" turned on, which showed nothing.

    I should have run Profiler as well, which would have put me right 🙁

    Since the stored procedure doesn't contain any DML statements, you would not have gotten an execution plan even if there had been no infinite recursion in the proc.

    That being said, actual DML statements that run into an error also do not produce an execution plan. Kinda makes sense because the actual row counts would be incomplete and could lead to bad tuning decisions, but on the other hand it would really be helpful for troubleshooting if we did get the plan of the failed query. But I guess it's never been a conscious decision, just an artefact of how run-time errors are handled internally.


    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/

  • Ed Wagner

    SSC Guru

    Points: 286969

    Nice question to get the brain moving. I know it sounds trivial and somewhat unrelated, but this is yet another reason why I indent. Of course, I guess it could be used to mislead the reader, but I find it helps to convey my intent...sometimes to myself. 😛

  • djj

    SSCoach

    Points: 18738

    Missed the chose four. Would have gotten it wrong anyway as I fell into the begin / end mind set.:-D

  • SqlMel

    SSCrazy

    Points: 2891

    I knew I had this from the start since I've made the mistake of not putting the GO statement before calling the SP, as shown in batch 1.

    After that, it was just a matter of reading carefully the answers.

    Good question.

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

  • dale_berta

    SSC Eights!

    Points: 942

    Interestingly enough, the documentation on CREATE PROCEDURE don't say anywhere, that I could find, that it had to be the the only statement in the batch. The links provided in the answer, for BEGIN END, and for GO, also weren't particularly helpful. I finally found the required information on the documentation for Batches, (2008 R2). The Rules for Using Batches is where I finally found that CREATE PROCEDURE, and a few other CREATEs, cannot be combined with other statements in a batch.

  • Hugo Kornelis

    SSC Guru

    Points: 64675

    dale_berta (5/14/2015)


    Interestingly enough, the documentation on CREATE PROCEDURE don't say anywhere, that I could find, that it had to be the the only statement in the batch. The links provided in the answer, for BEGIN END, and for GO, also weren't particularly helpful. I finally found the required information on the documentation for Batches, (2008 R2). The Rules for Using Batches is where I finally found that CREATE PROCEDURE, and a few other CREATEs, cannot be combined with other statements in a batch.

    It actually does say so in the CREATE PROCEDURE item at https://msdn.microsoft.com/en-us/library/ms187926.aspx. In the "Limitations and Restrictions" section: "The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch."


    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/

  • dale_berta

    SSC Eights!

    Points: 942

    So it does. I read through it several times, and didn't catch it. D'oh! Thank you Hugo. I must stand embarrassed for the appropriate amount of time.

  • Xavon

    SSCrazy

    Points: 2280

    I somehow read the options 2 &3 three as sproc is/is not created, not sproc is/is not executed. So I picked 2 instead of 3. Got 3/4, can I get 1.5 points? :hehe:

  • Sean Lange

    SSC Guru

    Points: 286515

    Good question. I am not really too surprised that so few people got this correct. It is a topic that comes up frequently around the forums. Like Hugo I tend to avoid using begin/end blocks in my procedures.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • SQL_Hunt

    SSC-Dedicated

    Points: 33283

    I got one wrong. I did not realize to answer correctly that why the store proc did not execute.....

    Thanks.

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

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