March 2, 2013 at 12:27 pm
I haven't tried this but i got it wrong (apparently). I don't think the proposers answer is correct though as SQL will terminate after 32 levels of nesting which is why i did not chose infinite loop as 32 is a long way off infinity.
This is an invalid question and I for one do not like getting told I am wrong when the question is actually wrong. It does not do my overall score any good if people are asking invalid questions and I am sorry but I resent that. None of the answers are correct in my opinion.
There will be a warning about sp2 not existing when referenced on creation of sp1 but this is not a syntax error. The table does not contain anything so nothing could every be returned but it will not loop infinitely because SQL server is such a good dB that it is almost impossible to get an infinite loop to happen due to protection.
This written using my useless nexus 7 tablet so sorry if bad spelling etc. I'm lucky just to get a post up.
David Bridge
David Bridge Technology Limited
www.davidbridgetechnology.com
March 4, 2013 at 12:39 am
Interesting question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 4, 2013 at 5:11 am
Danny Ocean (3/1/2013)
I think, this question is not well define. When you execute this code, it's execute and throw below errorMsg 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..
March 5, 2013 at 11:16 am
Tough one. Thanks.
March 6, 2013 at 4:11 am
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.
March 6, 2013 at 3:12 pm
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
March 6, 2013 at 6:01 pm
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.
March 9, 2013 at 12:48 pm
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)
March 11, 2013 at 10:04 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 11, 2013 at 1:21 pm
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
March 11, 2013 at 1:23 pm
deleted duplicate post
Tom
March 12, 2013 at 12:32 am
I have said syntax error. See SP2 is executed before it is created then how code will go in infinite loop then?
March 12, 2013 at 2:51 am
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
March 13, 2013 at 6:48 am
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.
March 13, 2013 at 7:25 am
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)
Viewing 15 posts - 61 through 75 (of 85 total)
You must be logged in to reply to this topic. Login to reply