Share via

Standard Parts of a SQL Server Stored Procedure

Anonymous
Aug 4, 2022, 6:34 PM

Just wondering what best practices are for standard parts of a SQL Server Stored Procedure.

  • Should you always SET NOCOUNT ON and why?
  • Should you always use a TRY...CATCH for error handling?
  • Should you always use BEGIN...END?

Just trying to standardize our SQL Server Stored Procedures and just curious as to what other shops do out there.

Thanks in advance.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,276 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,675 questions
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. Naomi Nosonovsky 8,051 Reputation points
    Aug 4, 2022, 7:13 PM

    Yes to all 3 questions in our setup although the third point is optional and more of a preference. I would also use SET XACT_ABORT, NOCOUNT ON; SET NOCOUNT OFF may lead to some weird hard to find out issues (I do remember we had one particular case long time ago and adding this statement resolved the problem).

    0 comments No comments

  2. Tom Phillips 17,741 Reputation points
    Aug 4, 2022, 8:43 PM

    As with most programming languages, it is up to you to determine your best practices and style. It is mostly personal preference.

    You should always use SET NOCOUNT ON, because it can cause problems with .Net programs calling the proc and can have a small performance increase. See: https://learn.microsoft.com/en-us/sql/t-sql/statements/set-nocount-transact-sql?view=sql-server-ver16#remarks

    Always use Try/catch, not necessarily. If all your catch is doing is throwing the original error, then there is no need. If you have a transaction which needs rolled back, or you want to log your error, etc, then you should have them.

    Begin/end is a style decision. It does not affect the proc execution in any way.

    There are several people who have documented best practices. I don't agree with all of them.

    https://sqlblog.org/2008/10/30/my-stored-procedure-best-practices-checklist
    https://www.sqlservercentral.com/articles/twenty-tips-to-write-a-good-stored-procedure-1


  3. Erland Sommarskog 115.1K Reputation points MVP
    Aug 4, 2022, 10:10 PM

    If I write a stored procedure, I typically start off with this skeleton:

       CREATE PROCEDURE MySP @var1 ... AS  
       SET XACT_ABORT, NOCOUNT ON  
       BEGIN TRY  
          -- Then I write the actual code of the procedure.  
       END TRY  
       BEGIN CATCH  
          IF @@trancount > 0 ROLLBACK TRANSACTION  
          ; THROW  
       END CATCH  
    

    The seemingly odd placement of the semicolon before THROW is due to that if you put it after TRANSACTION, you easily deleted by mistake. Unfortunately, what you get in that case is not a syntax error, but something which can result in the error messages being lost and the error that fired the CATCH handler to go unnoticed.

    0 comments No comments

  4. Bert Zhou-msft 3,436 Reputation points
    Aug 5, 2022, 5:49 AM

    Hi,anonymous user
    It all depends on your actual preference. If you want to implement standardized stored procedures, you can find the code that suits you by clicking on the template of the sql server interface, like this:

    -- ================================================  
    -- Template generated from Template Explorer using:  
    -- Create Procedure (New Menu).SQL  
    --  
    -- Use the Specify Values for Template Parameters   
    -- command (Ctrl-Shift-M) to fill in the parameter   
    -- values below.  
    --  
    -- This block of comments will not be included in  
    -- the definition of the procedure.  
    -- ================================================  
    SET ANSI_NULLS ON  
    GO  
    SET QUOTED_IDENTIFIER ON  
    GO  
    -- =============================================  
    -- Author:		<Author,,Name>  
    -- Create date: <Create Date,,>  
    -- Description:	<Description,,>  
    -- =============================================  
    CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>   
    	-- Add the parameters for the stored procedure here  
    	<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,   
    	<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>  
    AS  
    BEGIN  
    	-- SET NOCOUNT ON added to prevent extra result sets from  
    	-- interfering with SELECT statements.  
    	SET NOCOUNT ON;  
      
        -- Insert statements for procedure here  
    	SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>  
    END  
    GO  
    

    For specific operation, please refer to Fig.
    1)use “ctrl+alt+t” to open Template Browser;

    228368-image.png

    2)choose stored procedure;

    228369-image.png
    3)Choose the appropriate stored procedure according to your needs.

    In order to let you know better to learn how to write stored procedures, please visit this link.

    Bert Zhou


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.