Can This Work?

  • 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.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • 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:

  • 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.

  • 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]

  • Another game of "guess what the OP was driving at" rather than "do you understand SQL Server". Sorry, not impressed (again).

  • This was removed by the editor as SPAM

  • 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

  • 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

  • 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

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

  • The code won't execute. Create Proc has to be the first statement.

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

  • No - The create sp statement has to be the first statement in the batch, so, as written, the TSQL will not execute. (Try it.)

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

  • 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