Stored procedure best practice

In a stored procedure with several "sections...

Is it better to use a Bigin and End

Or place a semi-colon after each sectio with an overall Begin End?
Larry Bristersr. DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Define 'sections'.

Everyone's style is different, so you're likely going to get a lot of answers here.  
Below is mine, not that I'm claiming any genuis here, but here you go...


IF OBJECT_ID('my_sp') is not null
      DROP PROCEDURE my_sp
GO

CREATE PROC my_sp (@execution_id int, @step int, @success bit out) as
 
/*
-- TESTING ONLY, to run this SP manually comment out the above lines, and uncomment the block below.
Declare @execution_id int, @step int
SELECT @execution_id = 1, @step = 400
*/
     
/*
One-sentance description of what this SP does.

This SP has the following sections, all marked with a comment repeated three times:

Table of Contents of this SP:
Section 1                  What Section 1 does
Section 2                  What Section 2 does
etc.

Parameters:
@execution_id            EXECUTIONS.ID of this run.  Needed to write to EXECUTION_STEPS.
@step                        FIRST EXECUTION_STEPS.step value, each subsequent INS adds 1 so the steps are sequential.
@dt                 The SSIS package will pass the Last Modified date of the XML file.  If not, then GETDATE.

Version History:
12-05-11  jhorn   Original
12-06-11  jhorn   Change #1.  Spell it out.
      
*/

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

-- SET TRANSACTION ISOLATION LEVEL {whatever}


-- SECTION 1
-- SECTION 1
-- SECTION 1

-- section 1 SQL

-- SECTION 1
-- SECTION 1
-- SECTION 1

-- section 2 SQL

-- IF any errors, SELECT @SUCCESS = 0, RETURN, and handle it in the calling SP/whatever.

-- If no errors, success.
SELECT @success = 1

GO
0
GiantSlayerCommented:
I would use one overall BEGIN / END and statement terminators (;) after each stement like:

CREATE PROCEDURE dbo.proc
AS
BEGIN
   SELECT * FROM table1;
   GO
   SELECT * FROM table2;
END
GO
0
Scott PletcherSenior DBACommented:
You don't need a BEGIN at the start of a procedure.  I would only use BEGIN ... END when you need to, otherwise you can run into scope issues with certain types of variables.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Larry Bristersr. DeveloperAuthor Commented:
ScottPletcher:
So a begin end on the outside  and the simi-colon at the end of sections...with comments?
0
Scott PletcherSenior DBACommented:
There's no need for an "outside" BEGIN ... END encasing the code in a stored proc.

Contrary to what you often see, a BEGIN is not needed at the start of a proc, so this:

CREATE PROC Proc1
AS
SET NOCOUNT ON
SELECT GETDATE()
GO

is fine.

I usually just put a line of asterisks and comments between logical sections of code.

Adding a BEGIN sets a scope in SQL, which could be a side effect you don't want just from an attempt to logically segment / document code.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Larry Bristersr. DeveloperAuthor Commented:
You've given me a lot of great information over the past couple of years.

Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.