SQL Server Coding Standards: Working with Stored Procedures

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Prepared by Bihag Thaker

Prefix the Stored Procedure Name with ‘usp_’.

Try to include only letters in identifiers by using Pascal Casing and avoid the use of special characters in identifiers. Underscore (_) character may be used for element separation in an identifier.

Here element means Application Prefix, type of Operation, Table Name or Entity Name etc. However, word separation in single element should be achieved with PascalCasing only.

Use proper, meaningful and self-explanatory identifiers for stored procedures. Stored procedures are generally used for different actions like INSERT, UPDATE, DELETE and SELECT operations.

Following naming convention should be used for stored procedures:
‘usp_’ + + ‘_’ + + /

Here, part can be optional. can be a verb like ‘Add’, ‘Insert’, ‘Update’, ‘Change’, ‘Set’, ‘Delete’, ‘Remove’, ‘Select’ and ‘Get’ and so on. Some of the examples are:

1 2 3 4

usp_Sales_GetOrderDetails usp_Sales_InsertOrderDetails usp_Sales_UpdateOrderDetails usp_Sales_DeleteOrderDetails

Use schemas to separate different sets of stored procedures across multiple applications when possible. For example, if schema Sales is used instead of ‘Sales_’ as Application Prefix, then above Stored Procedures should be as follows:

1 2 3 4

[Sales].[usp_ GetOrderDetails] [Sales].[usp_ InsertOrderDetails] [Sales].[usp_ UpdateOrderDetails] [Sales].[usp_ DeleteOrderDetails]

Create stored procedures and try to implement functionalities through stored procedures wherever possible rather than using ad-hoc queries from the application.

While creating stored procedures, specify the name of the schema explicitly within which the stored procedure is to be created even if it is dbo.

Return the success or failure status from the stored procedure with the use of RETURN statement. Value 1 should be used for success and 0 for failure.

If a stored procedure is required to return some value, do not use RETURN statement. The RETURN statement is not intended for this purpose. Consider using OUTPUT parameters as required in stored procedures and pass output values from OUTPUT parameters to the application.

Use @ErrorCode OUTPUT parameter to communicate with the application any error occurred in the stored procedure.

When possible, consider single stored procedure call which returns multiple result sets to the application rather than calling stored procedure multiple times for each different result set. This reduces round trips to the database server and improves the application response time.

Do not write very lengthy stored procedures. Try to keep them as short as possible.

If business logic which needs to be implemented within a stored procedure is very lengthy, then try to break the business logic into different modules and implement each different module in its separate stored procedure and call those sub-stored procedures in the main stored procedure.

Use SET NOCOUNT ON at the beginning of the stored procedures whenever possible. This can improve the performance of the queries.

Consider declaring variables at the top of stored procedures.

Do not use temporary tables and table variables frequently. When possible, see if the same functionality can be achieved by using Table Expressions like derived tables and Common Table Expressions.

Avoid creating frequent temporary tables in IF conditions within the stored procedure.

Whenever intermediate result set needs to be stored within a stored procedure, consider implementing temporary table approach and table variable approach. Compare the results between two approaches and choose the one which is efficient.

Always use transactions in the stored procedures when multiple DML statements are performed within a unit of work.

Keep transactions as short as possible to reduce locking issues.

Do not use WITH RECOMPILE option with the stored procedures.

Following is the sample template of a stored procedure. It has been provided here only to have a basic idea of coding structure and standards that a stored procedure should follow: