March 1, 2013 at 6:56 am
This is a fun question. Of course, the code will fail the syntax validation if executed all at once. Two proc call each other, eventually SQL Server will freak out. : )
I guess one possible answers for this question is yes, since the 4th step was able to execute x-number of time.
thanks for the question.
--------------------------------------------------------------------------------------
Hai Ton
My Db4Breakfast blog.
March 1, 2013 at 7:33 am
If you run the code as supplied they it fails with a syntax error. If you run each part but it's self you get a warning creating the sp1. If you continue and run the last you get an infiate loop.
What is the writer trying to show us?
March 1, 2013 at 7:33 am
Good question, this is truly an infinite loop, only the limitation of the platform prevents this executing an infinite number of times. If SQL server in some future version were to have an unlimited stack buffer to handle all of the calls from sp1 to sp2 and back again then this would execute infinitely.
March 1, 2013 at 7:38 am
This was a good question and there's been a bit of "OP bashing" here.
The question asked if an infinite loop was created which it definitely is. The fact that the process does not run ad infinitum is to do with how the environment handles an infinite loop.
March 1, 2013 at 7:41 am
paul.knibbs (3/1/2013)
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.
And me, think a little more clue was needed than 'Will it execute?' as three possible answers could have been correct.
March 1, 2013 at 7:42 am
Executed exactly as provided you will get the following on SQL Server 2008 R2
Msg 111, Level 15, State 1, Procedure sp1, Line 12
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
Msg 156, Level 15, State 1, Procedure sp1, Line 17
Incorrect syntax near the keyword 'proc'.
That in my humble opinion is a syntax error. Poorly formed question.
March 1, 2013 at 8:09 am
Interesting question, but like so many others, could have been worded better and/or used some vetting. I disagree with the answer, because the stored procedure does execute successfully 32 times before it encounters the error.
Hakim Ali
www.sqlzen.com
March 1, 2013 at 8:20 am
The idea of the question is nice, but the execution is poor.
The code as posted will generate a syntax error, because there are no batch seperators. The question text includes no indication that they should be executed as seperate batches.
Also, when batch seperators are assumed, there is no correct answer. The first problem is that the term "successfully" in the question is undefined. What is the intention? If the intention is to get 32 empty result sets and an error message - it does exactly that! I disagree with the official correct answer because (a) "no" suggests either refusal to create the procedures or refusl to execute at all, and (b) the loop is not infinite, it will be aborted once the nesting level reaches 32.
With batch seperators assumed, the correct answer would be "procs are created and code runs, but will be aborted and generate an error message after 16 executions of each proc".
March 1, 2013 at 8:23 am
Bottom line, syntax error should be the correct answer. I saw the infinite loop, but figured that was a rouse to hide the fact that there were no "go" separators and that the code would just produce a syntax error.
Its akin to asking what is 2 + 4? 3, 5, 6 or 11
What is it, you ask? Why, 11 of course...
In base 5...
March 1, 2013 at 8:41 am
I guess perceptions are different. With the comments noting
-- first step
...
-- second step
...
I executed each section of code until the next comment, since that made sense to me. Executing the entire thing does bring about an error.
I added GOs and BEGIN/END terminators to make the code clearer.
For those of you saying this isn't an infinite loop, I would argue that is incorrect. An infinite loop is one that will keep running until the limitations of the platform kick in because it has no terminating expression. There is not such thing as an infinite loop otherwise. All infinite loops terminate, usually when heap space or working space is exhausted. This is an infinite loop.
March 1, 2013 at 9:03 am
Steve Jones - SSC Editor (3/1/2013)
I guess perceptions are different. With the comments noting-- first step
...
-- second step
...
I executed each section of code until the next comment, since that made sense to me. Executing the entire thing does bring about an error.
If the comments had read "first batch" / "second batch" / etc, I would agree. But since a batch can contain multiple statements, I interpreted the steps as statements in a batch. When reading the code I figured that there would be references to it in the question (like "error in step 3"), and once I had clicked through to the website and checked the answers I had forgotten about it.
Thanks for editing the question!
For those of you saying this isn't an infinite loop, I would argue that is incorrect. An infinite loop is one that will keep running until the limitations of the platform kick in because it has no terminating expression. There is not such thing as an infinite loop otherwise. All infinite loops terminate, usually when heap space or working space is exhausted. This is an infinite loop.
It is very easy to create infinite loops that will never terminate. For instance:
DECLARE @i = 0;
WHILE @i < 10;
BEGIN
SET @i = 1; -- Type, should have been SET @i += 1;
END;
March 1, 2013 at 9:09 am
Thanks for the question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 1, 2013 at 9:34 am
Steve Jones - SSC Editor (3/1/2013)
I guess perceptions are different. With the comments noting-- first step
...
-- second step
...
I executed each section of code until the next comment, since that made sense to me.
I agree on that - seemed obvious what was meant.
Steve Jones - SSC Editor (3/1/2013)
For those of you saying this isn't an infinite loop, I would argue that is incorrect. An infinite loop is one that will keep running until the limitations of the platform kick in because it has no terminating expression. There is not such thing as an infinite loop otherwise. All infinite loops terminate, usually when heap space or working space is exhausted. This is an infinite loop.
Disagree with that one though 🙂
Hugo's given a SQL example.
A simple Basic infinite loop would be
10 goto 10
I assumed the point of the question was to see whether we knew that SQLServer would teminate the loop after 32 iterations. I did know, so got it wrong 😉
March 1, 2013 at 9:35 am
The code in the email does not have GO in it but the code in the Web page does so it is not clear what is being tested here. Yes they are mutually referencing procedures but as noted, the system stops on its own after 32 nestings. Not sure whether to call that a success or not.
March 1, 2013 at 9:46 am
Fair enough, I was thinking of many CS problems that loop until space is exhausted.
I'd still argue this is an infinite loop in code. It's the hardware and environment that terminates it, not the code.
However I can reword that if enough people feel that's a misleading answer.
Viewing 15 posts - 31 through 45 (of 85 total)
You must be logged in to reply to this topic. Login to reply