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).
Standard Parts of a SQL Server Stored Procedure
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.
4 answers
Sort by: Most helpful
-
-
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 -
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.
-
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;2)choose stored procedure;
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