May 14, 2015 at 8:33 am
I use BEGIN and END for readability. Though, I have been doing this for years, it was only recently that I was burnt. I passed the stored procedure to a colleague via email, but somehow it was altered before execution. I am assuming it was a cut and paste mistake where the cursor was not where it was thought to be.
I had always assumed that it marked the end of the procedure as it appears. Appearances, assumptions, and perceptions can be very deceptive.
May 14, 2015 at 8:59 am
Good question, Robert. I got burned, because I always use BEGIN..END in my procs for readability, but I always put GO after them to end the block. When I read the question, I wondered whether the intent was to show that you had to use GO to end the proc definitiion, but I went with my gut instinct (no problems), which was wrong.
May 14, 2015 at 9:16 am
Got it wrong but really like the question. Thanks, Robert!
May 14, 2015 at 11:04 am
Very nice question, thanks!
May 14, 2015 at 11:42 pm
Msg 217, Level 16, State 1, Procedure Test, Line 5
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
And line 5 is the 5th line of the first batch which is:
-- Batch 1
CREATE PROCEDURE dbo.Test AS
BEGIN
SET NOCOUNT ON;
END;
-- Line five it is :EXEC dbo.Test;
GO
May 15, 2015 at 4:42 am
please check the definition of the stored procedure
sp_helptext 'Test',
then you will understand
May 15, 2015 at 4:45 am
good question robert
May 17, 2015 at 7:55 pm
Nice clear and straightforward question.
Given the vast proportion (three quarters) of wrong answers, it evidently isn't as easy as it looks. That surprised me. I guess people are used to Algol-like languages, where begin (or {) end (or }) blocks have a more general usage than in SQL.
Forcing various things - including procedure declarations - to be in separate batches seems to me to be a lovely illustration of the incompetence of the language designers/standards committees, but having played with SQL for more than 3 decades I'm used to that sort of nonsense and it no longer bothers me.
Tom
May 18, 2015 at 1:44 pm
Thanks for the question Robert.
May 19, 2015 at 7:27 am
The question is incorrect. The procedure is executed 32 times. Or there must be an answer regarding recursion.
May 19, 2015 at 7:54 am
v.malyutin (5/19/2015)
The question is incorrect. The procedure is executed 32 times. Or there must be an answer regarding recursion.
It executes inside an implicit transaction until it raises an exception and then rolls back the transaction. Hence the answer "Batch 2 generates an error".
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 19, 2015 at 10:09 am
Sean Lange (5/19/2015)
v.malyutin (5/19/2015)
The question is incorrect. The procedure is executed 32 times. Or there must be an answer regarding recursion.It executes inside an implicit transaction until it raises an exception and then rolls back the transaction. Hence the answer "Batch 2 generates an error".
The exception is not about the sintax. But the question is all about this, as I think. Besides, there were 32 calls of SP until the exception is raised. They were seccessful, actually. And if there is not a limit of recursion, the exception is not raised.
May 19, 2015 at 12:42 pm
v.malyutin (5/19/2015)
Sean Lange (5/19/2015)
v.malyutin (5/19/2015)
The question is incorrect. The procedure is executed 32 times. Or there must be an answer regarding recursion.It executes inside an implicit transaction until it raises an exception and then rolls back the transaction. Hence the answer "Batch 2 generates an error".
The exception is not about the sintax. But the question is all about this, as I think. Besides, there were 32 calls of SP until the exception is raised. They were seccessful, actually. And if there is not a limit of recursion, the exception is not raised.
There is no syntax error. There is nothing indicating anywhere there is a syntax error. The question is about your understanding of begin and end as it relates to ending and beginning batches.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 20, 2015 at 4:37 am
Great question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 21, 2015 at 1:43 am
Nice question.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply