May 13, 2015 at 10:36 pm
Comments posted to this topic are about the item Stored Procedure Creation
May 14, 2015 at 12:53 am
That's true! Stack overflow!
May 14, 2015 at 2:15 am
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)
May 14, 2015 at 3:49 am
This was removed by the editor as SPAM
May 14, 2015 at 4:33 am
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 🙁
May 14, 2015 at 4:47 am
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.
May 14, 2015 at 5:23 am
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. 😛
May 14, 2015 at 5:35 am
Missed the chose four. Would have gotten it wrong anyway as I fell into the begin / end mind set.:-D
May 14, 2015 at 5:51 am
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. 😎
May 14, 2015 at 6:11 am
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.
May 14, 2015 at 6:15 am
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."
May 14, 2015 at 6:20 am
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.
May 14, 2015 at 6:39 am
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:
May 14, 2015 at 7:43 am
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/
May 14, 2015 at 8:07 am
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 31 total)
You must be logged in to reply to this topic. Login to reply