January 18, 2012 at 3:08 am
Hello,
As someone who is feeling my way through the long learning curve of T-SQL and SQL Server in general, I've been pondering a basic question with regards to stored procedures.
After reading various tutorials on the matter there seems to be no consistency in the way the BEGIN ... END block is applied. In some examples, the stored procedure body starts with a BEGIN and ultimately ends with a matching END and sometimes even a GO. Other examples do not wrap the procedure inside a BEGIN and END, but seem to work just fine.
I may be being pedantic or overly concerned with trivia here, but I'd like to know why the BEGIN/END structure should or should not be used.
January 18, 2012 at 3:16 am
It makes no difference at all to the execution plan, it's a matter or personal preference or what coding standards you choose to adopt.
For me, I prefer the global BEGIN and END block on all sprocs as it improves readablity, at least for short stored procedures.
Rob
January 18, 2012 at 3:24 am
Thanks for that Rob. 🙂
January 18, 2012 at 3:30 am
You need to use it, for example, if you have a code block longer than one line to be executed (or not executed) in an IF construction. Otherwise, it's not required. I prefer not to use it unless it's required, especially where you have lots of IFs in your code and the addition of unnecessary BEGINs and ENDs causes unnecessary confusion. There's no right or wrong, though - it's down to personal preference.
John
January 18, 2012 at 3:46 am
John Mitchell-245523 (1/18/2012)
You need to use it, for example, if you have a code block longer than one line to be executed (or not executed) in an IF construction. Otherwise, it's not required. I prefer not to use it unless it's required, especially where you have lots of IFs in your code and the addition of unnecessary BEGINs and ENDs causes unnecessary confusion. There's no right or wrong, though - it's down to personal preference.John
Hi John,
I think you've misunderstood exactly where the BEGIN/END question I posed relates to. I understand that multi line IF statements, require the BEGIN and END wrapped around the statements to be executed for that IF.
It was actually the need to wrap the entire stored procedure body within a BEGIN .. END block that was the crux of my question.
Rob has explained that they make no difference other than personal preference in this regard, so I think he nailed it with that answer. I was concerned whether there was some kind of functional or best practice reason for wrapping a stored procedure body in the BEGIN.END block, but it appears not.
Thanks for taking the time to reply as all comments are appreciated 🙂
January 18, 2012 at 4:27 am
I prefer using BEGIN/END because it prevents errors.
Consider this example:
CREATE PROCEDURE doSomething
AS
UPDATE SomeTable
SET SomeColumn = SomeValue
WHERE SomeCondition IS NOT NULL
SELECT SomeColumn
FROM SomeTable
WHERE SomeCondition IS NOT NULL
It certainly works, but I could highlight part of this procedure (say the first statement) and execute it in SSMS. The procedure would contain just the first statement.
If I highlighted the whole code and hit F5, the procedure would contain both statements.
Another possible ambiguity: the code is intentionally indented as if the second statement did not belong to the procedure, and, at a first look, you could expect things to be like that. BEGIN/END makes everything more explicit and less error-prone IMHO:
CREATE PROCEDURE doSomething
AS
BEGIN
UPDATE SomeTable
SET SomeColumn = SomeValue
WHERE SomeCondition IS NOT NULL
SELECT SomeColumn
FROM SomeTable
WHERE SomeCondition IS NOT NULL
END
That said, it's just personal preference.
-- Gianluca Sartori
January 18, 2012 at 4:38 am
Hi,
Hmmm ... given the examples you've provided, I can see a compelling reason to use the BEGIN/END block to wrap stored procedures in. Personally, I am quite fussy about code legibility and indenting statements to provide clarity and isolation etc.
I think for the reasons you gave I will make a mental effort to use the BEGIN/END and indent the procedure between them accordingly.
There!! 🙂 I knew I just needed a good excuse to use them 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply