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)
March 13, 2013 at 8:11 am
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