Can This Work?

  • Interesting question, thanks.

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

  • Danny Ocean (3/1/2013)


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

    With keeping this in mind, I answered YES. Because it's not infinite loop.

    --
    Dineshbabu
    Desire to learn new things..

  • Tough one. Thanks.

  • I guessed the wrong answer as the proc1 is using proc2, While as Proc2 not exist . So Ideally it should throw warning as well as error message.

  • Hello

    your question is:

    Can This Work?

    Will this code execute and return a value?

    and the answer is YES

    you can compile an create the table, create the stored procedure, with warnings but you can create

    if you execute the sp1 it works and return values

    but environment limits to 32 execution because its a loop

    i'm not wrong, i think your question is wrong

  • mtassin (3/1/2013)


    And in either case, Yes it returns values... 32 times, at which point it hits max recursion and returns an error.

    None of the answers were perfectly correct, and it came down to determining the author's intent.

    +1 for this.

    I got it wrong but after a discussion here at work we came up with a possible reason where answer 3 could be correct.

    SSMS returns results on the fly so we get results before we hit max recursion but if this was being run through a web interface it might never return any results.

    I inserted a record to the table as the discussion here centred on each proc not returning any results as they technically never finish.

    SSMS returns 32 rows before it reaches its nesting limit.

    I have been told not to put this into one of our reporting services so cant really test the web interface side of things.

    I have a hard time accepting theory, I generally need to see it work/not work before I am convinced but in this case I may have to.

  • stormcricket (3/6/2013)


    I got it wrong but after a discussion here at work we came up with a possible reason where answer 3 could be correct.

    SSMS returns results on the fly so we get results before we hit max recursion but if this was being run through a web interface it might never return any results.

    The code behind the web page can of course be programmed to do anything the developer likes, but SQL Server will definitely return 32 result sets and then an error message to the web page (the client for SQL Server in this case)


    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/

  • 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

  • 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

    It includes every line of code up to the next batch separator (GO if you haven't redefined it) whether you have BEGINs and ENDs or not. That's an extremely bad chunk of language design, I reckon, but that's what it does.

    Tom

  • deleted duplicate post

    Tom

  • I have said syntax error. See SP2 is executed before it is created then how code will go in infinite loop then?

  • Err, I want my point back. No infinite loop because nestlevel stops us at 32. Also, it does return some resultsets, 32 to be exact, before hitting the error. The wording of the answer choices could have been better.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • In 2nd step we wil get like this

    Yes, It will create infinite loop but it will end with maximum nesting level on 32

    The module 'sp1' depends on the missing object 'sp2'. The module will still be created; however, it cannot run successfully until the object exists.

  • Sorry folks, but the correct answer is NOT among the choices. This question and all points associated should be removed from the system, and it's about time these questions were PROPERLY TESTED so as to ensure there that the COMPLETELY CORRECT ANSWER is ALWAYS PRESENT among the available answer choices, and that ALL QUESTIONS WITHOUT THE COMPLETELY CORRECT ANSWER AMONG THE CHOICES are REJECTED. Why do we try so hard to include what amounts to a trick question that doesn't even have the completely correct answer in it? And why isn't anyone making a lot more noise about it? Have we given up on ensuring that the educational value remains? What do we learn by discussing a question like this over 5 pages of replies? Not much...

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • sgmunson (3/13/2013)


    And why isn't anyone making a lot more noise about it?

    Because it's not really important? Because we're all grateful for the free service that Steve provides, and the time and effort spent by all the question setters?

Viewing 15 posts - 61 through 75 (of 85 total)

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