Stored Procedure Best Practices

Naming Convention for Database DesignHow to Count Number of Rows and Columns in a table with Query?Difference between MS SQL 2005 & SQL Server 2000SQL Commands - Create, Drop and Alter Database in SQL Server 2008 etc.  

In this article I will explain about the standardize way to write Stored Procedure in SQL Server. It consists of guideline for standardization and best practices for writing stored procedure in SQL Server. Find the best way to write SP in SQL Server. 

best practices,good practices, how to write SQL Server Stored Procedures, writing stored procedures, good naming practices, creating stored procedures, database,SQL Server 2000 / 2005 / 2008

Convention and Readability 

Writing of stored procedures should be in the way it should readable and easy to understand by another developer.
  • Consistent Formatting (Proper Indentation)
  • Write all SQL Server keywords in capital letters i.e. ( SELECT, FROM, WHERE etc.)
  • Write the stored procedure name with full names i.e. (CREATE PROCEDURE [dbo].EmployeeSalaryCalculation)
  • Declare the DECLARATION and INITIALIZATION at the beginning of the stored procedure.
  • Do not write the stored procedure name beginning with "sp_" it uses for system procedure.
  • Use "SET NOCOUNT" ON option in the beginning of the stored procedure.
  • Use column names with select command instead of using (Select *)
  • Set the default value and size of the parameters
  • Use the "Try Catch" statement
  • Use output statement to return single value rather than a table
  • Use CASE statement rather than nested IF ELSE statements
  • Use proper and consistent naming schema i.e. ({target}_{verb}) for example: dbo.Customer_Create, dbo.Customer_Update, dbo.Customer_Delete, dbo.Customer_GetList, dbo.Customer_GetDetails
  • Use BEGIN/END liberally
  • Use statement terminator (;)
  • Use parentheses liberally around AND/OR blocks
  • Use the sp_executesql stored procedure instead of the EXECUTE statement.

Manageability Guidelines 

  • Purpose, Creator, Date should be mention in the beginning of store procedure.
  • Use proper comments.
  • Complete history of changes stored procedure should mention in the beginning of store procedure with "Change Objective:", "Change Date" and "Change By"
  • Print the appropriate information using "PRINT" command to understand and view the execution steps.

Performance Improvement of Stored Procedure

  • Use while loop instead of Cursor
  • Use IF EXISTS (SELECT 1) instead of (SELECT *) i.e. ("IF EXISTS (SELECT 1 FROM sysobjects WHERE name ='MyTab' and AND type='U')")
  • Use table variable instead of temp table
  • Avoid sub-queries and use INNER JOIN
  • Use the ORDER BY and DISTINCT, TOP only when requires
  • Use proper indexing and not use indexing on the column which is not uses with where clause.
Please leave your comments, suggestions and queries about this post in the comment sections in order for me to improve my writing skills and to showcase more useful posts. Thanks for reading! :)

Post a Comment