Writing Stored Procedure in SQL - Best Practice

SQL Stored Procedures

A stored procedure is a group of Transact- SQL statements compiled into a single execution plan. So if you think about a query that you write over and over again, instead of having to write that query each time you would save it as a stored procedure and then just call the stored procedure to execute the SQL code that you saved as part of the stored procedure. 

In addition you can pass parameter to the stored procedure, so depending on what the need is the stored procedure can act accordingly based on the parameter values that were passed.

Read more about how to create stored procedure in sql server 2008 with example

Best Practice Creating A Stored Procedure

In this article I will share the guideline for standardization or best practices for writing sql stored procedures in database. Follow the below guidelines:-

SQL Server Database

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.

Conclusion: It was fun in learning and writing an article on the guideline for standardization or best practices for writing sql stored procedures. I hope this article will be helpful for enthusiastic peoples who are eager to learn and implement some interesting stuffs in new technology. 

Please feel free to comment your opinion about this article or whatever you feel like telling me. Also if you like this article, don't forget to share this article with your friends. Thanks!