March 1, 2013 at 2:31 am
Guessed the mind of the creator of the question correctly, so got it right. However, I think there is some ambiguity in how the question is phrased so it should perhaps be edited by a natural English speaker.
March 1, 2013 at 3:36 am
Well I assumed that the purpose of the question was to know that CREATE PROCEDURE has to be the first statement in a Batch and therefore the code as listed was going to fail with a syntax error.
I also knew that unless you really force it by overriding the limits using statements that were not present that SQL Server will not allow infinite recursion.
Therefore the "correct" answer could not possibly be the right answer and I feel robbed!:w00t:
March 1, 2013 at 3:44 am
Sean Pearce (3/1/2013)
The question is "Will this code execute successfully?"and the only possible answer is "NO".
Msg 111, Level 15, State 1, Procedure sp1, Line 11
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
Msg 111, Level 15, State 1, Procedure sp1, Line 19
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
+1. Got it wrong because my psychic powers were not sufficient to guess whether the user was asking a question about recursion or one about creating procedures in a batch.
March 1, 2013 at 4:10 am
Fortunately I guessed right but I agree with all the comments above.
Even assuming you were not referring to creating stored procedures in a batch, the answer for me was still not clear.
Is 'nesting level exceeded' considered a syntax error?
Yes, the code executes... at least until nesting level exceeded.
Is it an infinite loop when it stops without user intervention with an error?
Too many questions to answer.
I was lucky.
_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]
March 1, 2013 at 4:37 am
Another game of "guess what the OP was driving at" rather than "do you understand SQL Server". Sorry, not impressed (again).
March 1, 2013 at 4:38 am
This was removed by the editor as SPAM
March 1, 2013 at 5:25 am
Where I got tripped up was here, and I guess I'm still not sure I understand...
create proc sp1
as
select * from temp1
exec sp2
Without a BEGIN and END defining the body of proc sp1, I assumed it would only use the first statement, like an IF...THEN statement does. Then it would try to exec sp2 as a line separate from the SP definition - and since sp2 doesn't exist, it would throw an error.
I'm not entirely clear why that wouldn't happen. If you don't use BEGIN and END in your proc definition, does it simply include every line of code that follows? At least until it hits a GO?
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
March 1, 2013 at 5:28 am
ronmoses (3/1/2013)
Where I got tripped up was here, and I guess I'm still not sure I understand...
create proc sp1
as
select * from temp1
exec sp2
Without a BEGIN and END defining the body of proc sp1, I assumed it would only use the first statement, like an IF...THEN statement does. Then it would try to exec sp2 as a line separate from the SP definition - and since sp2 doesn't exist, it would throw an error.
I'm not entirely clear why that wouldn't happen. If you don't use BEGIN and END in your proc definition, does it simply include every line of code that follows? At least until it hits a GO?
ron
that was exactly my thought process
March 1, 2013 at 5:44 am
ronmoses (3/1/2013)
Where I got tripped up was here, and I guess I'm still not sure I understand...
create proc sp1
as
select * from temp1
exec sp2
Without a BEGIN and END defining the body of proc sp1, I assumed it would only use the first statement, like an IF...THEN statement does. Then it would try to exec sp2 as a line separate from the SP definition - and since sp2 doesn't exist, it would throw an error.
I'm not entirely clear why that wouldn't happen. If you don't use BEGIN and END in your proc definition, does it simply include every line of code that follows? At least until it hits a GO?
ron
+1
March 1, 2013 at 5:52 am
Concerning the complete code the solution will not work.
It will produce an error 111 because the GO is missing in between the single steps.
Better you check your own code before posting a QuoD 😉
Microsoft Certified Master: SQL Server 2008
MVP - Data Platform (2013 - ...)
my blog: http://www.sqlmaster.de (german only!)
March 1, 2013 at 6:09 am
The code won't execute. Create Proc has to be the first statement.
March 1, 2013 at 6:12 am
I think, this question is not well define. When you execute this code, it's execute and throw below error
Msg 217, Level 16, State 1, Procedure sp2, Line 5
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
March 1, 2013 at 6:24 am
No - The create sp statement has to be the first statement in the batch, so, as written, the TSQL will not execute. (Try it.)
March 1, 2013 at 6:35 am
p.govindaraj (3/1/2013)
Yes, It will create infinite loop but it will end with maximum nesting level on 32
I love that - a finite infinite loop!
March 1, 2013 at 6:39 am
ronmoses (3/1/2013)
Where I got tripped up was here, and I guess I'm still not sure I understand...
create proc sp1
as
select * from temp1
exec sp2
Without a BEGIN and END defining the body of proc sp1, I assumed it would only use the first statement, like an IF...THEN statement does. Then it would try to exec sp2 as a line separate from the SP definition - and since sp2 doesn't exist, it would throw an error.
I'm not entirely clear why that wouldn't happen. If you don't use BEGIN and END in your proc definition, does it simply include every line of code that follows? At least until it hits a GO?
ron
Hi,
BEGIN and END are not required for stored procedures. Therefore your assumption is correct: The proc includes everything that follows until it hits a "GO" (or whatever your batch separator may be).
Best Regards,
Chris Büttner
Viewing 15 posts - 16 through 30 (of 85 total)
You must be logged in to reply to this topic. Login to reply