Can This Work?
-
Comments posted to this topic are about the item Can This Work?
-
I got it right, but it was a sheer guess on the mind of OP ..
will this code execute successfully
it should have been mentioned as the 4th step..
also, it does execute ; so I guess the option should be like "Yes, but it will end up in an infinite loop."
or does the OP mean by the sql server message "command executed successfully " :unsure:
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm: -
I got tricked with this one. I said No, because the code will cause a syntax error as you can't mix creating tables and procs without using a GO
I guess I should have been looking at the bigger picture :blush:
-
tom.kane (2/28/2013)
I got tricked with this one. I said No, because the code will cause a syntax error as you can't mix creating tables and procs without using a GOI guess I should have been looking at the bigger picture :blush:
well, I think that's not the case .. It does create the stored procedure without the GO ..
One thing that could have caused a confusion was SP1 creating when SP2 is not created ; It gives a Warining , not an error , then creates the store procedure ...:-D
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm: -
Looking at the question again, if I had looked at the code as individual steps to be run separately then yes there is no syntax error. My bad.
-
demonfox (2/28/2013)
I got it right, but it was a sheer guess on the mind of OP ..will this code execute successfully
it should have been mentioned as the 4th step..
also, it does execute ; so I guess the option should be like "Yes, but it will end up in an infinite loop."
or does the OP mean by the sql server message "command executed successfully " :unsure:
Same with me...I guessed the correct answer..
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
I guess answer is Yes as it compiled and executed successfully but sql server restricts for nested looping to 32 level so it fails after execution of 32 times.
-
Yes, It will create infinite loop but it will end with maximum nesting level on 32
-
There is definitively no infinite loop as it throws an error when the max nest level has been reached.
Best Regards,
Chris Büttner
-
demonfox (2/28/2013)
tom.kane (2/28/2013)
I got tricked with this one. I said No, because the code will cause a syntax error as you can't mix creating tables and procs without using a GOI guess I should have been looking at the bigger picture :blush:
well, I think that's not the case .. It does create the stored procedure without the GO ..
One thing that could have caused a confusion was SP1 creating when SP2 is not created ; It gives a Warining , not an error , then creates the store procedure ...:-D
No, the procedure is not created:
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'.
Best Regards,
Chris Büttner
-
Simply nice, thank you for posting.
got it wrong, i selected "yes", i cancelled the page transaction but it was too late... the answer got submitted.
and couple of concerns
1. The batch separator is missing "GO" (in my case) as it is giving the error like below when i execute the statements as one batch (under sql 2012 Expr Edt) and also the same error under SQL 2000.
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.
2. the execution will happen like 32 times and it breaks by itself by giving this error
Server: Msg 217, Level 16, State 1, Procedure sp2, Line 7
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
so this is not infinite loop.
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart. -
p.govindaraj (3/1/2013)
Yes, It will create infinite loop but it will end with maximum nesting level on 32No it is not infinite loop, as you said it executes 32 times - it is the limit of the execution. When the value is known it cannot be called as infinite. Infinite is something where it goes on and on and it makes the server go slow and may be some cases it takes 100% CPU and then the smart guy comes in and extracts this piece of code with their expert analysis and he kills the underlying query manually. (in my opinion infinity is not the word here)
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart. -
ashwani24 (2/28/2013)
I guess answer is Yes as it compiled and executed successfully but sql server restricts for nested looping to 32 level so it fails after execution of 32 times.no it does not fails - actually it is the rule/validation on the sever to monitor the number of times the sp is called and break the chain after the object calling hits the limit.
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart. -
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.
-
Christian Buettner-167247 (3/1/2013)
demonfox (2/28/2013)
tom.kane (2/28/2013)
I got tricked with this one. I said No, because the code will cause a syntax error as you can't mix creating tables and procs without using a GOI guess I should have been looking at the bigger picture :blush:
well, I think that's not the case .. It does create the stored procedure without the GO ..
One thing that could have caused a confusion was SP1 creating when SP2 is not created ; It gives a Warining , not an error , then creates the store procedure ...:-D
No, the procedure is not created:
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'.
I guess I should have been more clear about that ...
If executing the statements in "Steps"as provided ; the procedure will be created ..
If execute the code as a batch , it will throw the error mentioned as above in the Quote..
Yes, It wasn't specifically mentioned in the Qotd that the each "Step" should be executed as a batch; I assumed that the code is to be executed in steps, that's what step mean ;-).
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
Viewing 15 posts - 1 through 15 (of 85 total)
You must be logged in to reply to this topic. Login to reply