BEGIN and END in a SQL Server Stored Procedure

Question

What are the general standard/convention regarding using BEGIN and END in a SQL Server Stored Procedure?

I totally understand using BEGIN and END for complex SQL Server Stored Procedures to separate logical chunks of code, but should they necessarily be used for simple SQL Server Stored Procedures for like readability?

Would love to hear people's opinions on using BEGIN and END in SQL Server Stored Procedures.

Thanks for your review and am hopeful for some solid, good, quality replies.

Answer 1

They are completely optional and only used for "style". I don't use them unless required after IF/WHILE blocks.

Answer 2

Red-Gate SQL Prompt suggests adding them at the very top of the procedure and the end, so I always have

create or alter storedProcName

parameters
/*
Comment block

*/
AS
BEGIN

 SET NOCOUNT, XACT_ABORT ON;  

 declare...  

 begin try  
 end try  
 begin catch  
 end catch  

END
GO

Answer 3

As Tom says they are optional. Quite a few people add BEGIN-END for the entire procedure code:

   CREATE PROCEDURE ... AS  
   BEGIN   
      ...  
   END  

Being an old-timer with SQL I'm not of that school. Then again, about any piece of production code I write goes like this:

   CREATE PROCEDURE ... AS  
   BEGIN TRY  
      ...  
   END TRY  
   BEGIN CATCH  
     ...  
   END CATCH  

As for a piece of code inside a stored procedure, I tend to write like this:

   IF @x > 2  
       SELECT @x = 2  

I don't use BEGIN-END here. However, if it is a long SELECT statement I do:

   IF @x > 2  
   BEGIN   
       SELECT ...  
       FROM   ...  
       JOIN   ...  
       LEFT JOIN ...  
       WHERE ...  
       GROUP BY   
   END  

But this is certainly a matter of preference.

Answer 4

Hi @Bobby P
The BEGIN... END statement bounds a logical block of SQL statements. I often use the BEGIN...END at the start and end of a stored procedure and function. But it is not strictly necessary.

However, the BEGIN...END is required for the IF ELSE statements, WHILE statements, etc., where you need to wrap multiple statements.
Sometimes, the statement block can be nested. It simply means that you can place a BEGIN...END statement within another BEGIN... END statement. Like this:

BEGIN  
    DECLARE @name VARCHAR(MAX);  
  
    SELECT TOP 1 @name = product_name  
    FROM production.products  
    ORDER BY  list_price DESC;  
      
    IF @@ROWCOUNT <> 0  
    BEGIN  
        PRINT 'The most expensive product is ' + @name  
    END  
    ELSE  
    BEGIN  
        PRINT 'No product found';  
    END;  
END  

For more details, please refer to this article: SQL Server BEGIN END

Best regards,
LiHong

Answer 5

So just to clarify...the BEGIN and END will NOT Transactionalize the sequence...And what I mean by that if we have multiple DELETEs within a BEGIN and an END, if one of the DELETEs fails for whatever reason, it is NOT going to roll back the previous DELETEs that may have already run then?