SQL Server 2005 Stored Procedure Coding Guidelines and Best Practices

SQL Server 2005 Stored Procedure Coding Guidelines and Best Practices

Must read before start writing stored procedure in SQL server 2005

Do

1. Write SET NOCOUNT ON in top of the procedure
2. Every SELECT statement should return with WITH NOLOCK key word.
3. Procedure should be rerunnable, Use IF EXISTS statement …
4. procedure always should have return value or return parameter
5. Use output parameter wherever necessary
6. Each code (for loop, If else etc) should be in BEGIN .. END block. For conditional checks( if else), please follow the same.
7. Use 'Derived tables' wherever possible, as they perform better. Consider the following query to find the second highest salary from the Employees table:

SELECT MIN(Amount) FROM Department WHERE DeptID IN

( SELECT TOP 2 DeptID FROM Department ORDER BY

Amount Desc)

The same query can be re-written using a derived table, as shown

below, and it performs twice as fast as the above query:

SELECT MIN(Amount) FROM

( SELECT TOP 2 Amount FROM Department ORDER BY

Amount DESC) AS D

Don’t

8. Do not include any business logics in your stored procedures, build a Business Logic Layer for that. Keep to what SQL was made for, inserting, deleting and selecting data.
9. Do not use reserved words, if you can’t avoid the use square brackets like: [event]
10. Do not use ‘SP_’ or ‘SYS_’ as a prefix in the stored procedure name.
11. Do proper Error handling in the procedure : use sp_addmessage to create user defined messages and raiserror for raising and passing the error code and description back to calling application.
12. Try to avoid wildcard characters at the beginning of a word while searching using the LIKE keyword, as that results in an index scan, which defeats the purpose of an index. The following statement results in an index scan, while the second statement results in an index seek:

SELECT Ename FROM Employee WHERE Ename LIKE '%han'

SELECT Ename FROM Employee WHERE Ename LIKE 'A%n'

Also avoid searching using not equals operators (<> and NOT) as they result in table and index scans.

13. Avoid the creation of temporary tables while processing data as much as possible, creating a temporary table means more disk I/O. Consider using advanced SQL, views, SQL Server 2000 table variable, or derived tables, instead of temporary tables.

14. Do not use SELECT * in your queries. Always write the required Column names

after the SELECT statement, like:

SELECT CustomerID, CustomerFirstName, City

This technique results in reduced disk I/O and better performance.

15. Do not wtite insert statement without defining the column name like below

INSERT INTO LMS_ERROR_LOG VALUES

( @pProspectId, @pMethodName, @pErrorMsg,

getdate() )

Always define the column name in the insert statement like below

INSERT INTO LMS_ERROR_LOG

( PROSPECT_ID,METHOD_NAME, EXCEPTION,DATE_TIME ) VALUES

@ProspectId,@pMethodName,@pErrorMsg,getdate() )

16. Do not write multiple declare statement inside the procedure like

Declare @Fname nvarchar(100)

Declare @Mname nvarchar(100)

Instead of that you can write like below

Declare

@Fname nvarchar(100),

@Mname nvarchar(100)

I normally follow below guidelines too for consitancy

17. Stored procedure name should be in CAPS latters for consistancy
18. Do Proper indenting ( 2 tabs for start and same for next levels)
19. Stored procedure name should follow some standrad to identify as below PROJECT_NAME_<SELECT OR INSERT OR UPDATE OR DELETE>_<OPERATION or TABLE_NAME>
20. Global parameter start with "p" and local parameter start with "v" . and for OUT parameter name should @pOut<ParameterName>
21. Created By/Date and purpose must be in the procedure as comment
22. All key word should be in the CAPITAL latters

Sample Stored Procedure

IF EXISTS (SELECT * FROM dbo.sysobjects

WHERE id = object_id(N'[dbo].[LMS_INSERT_ERROR_LOG]')

And OBJECTPROPERTY(id, N'IsProcedure') = 1)

DROP PROCEDURE [dbo].[LMS_INSERT_ERROR_LOG]

GO

CREATE PROCEDURE SP_LMS_INSERT_ERROR_LOG

@pProspectId INT,

@pMethodName NVARCHAR(100),

@pErrorMsg NVARCHAR(1000)

AS

/**

Created By/Date : Ritesh Kesharwani / 16th Jun 2008

Purpose : Log error into LMS_TRN_ERROR_LOG table

Modified History

Name/Date Purpose

Ritesh/25th July 2008 Added new parameter for business change

**/

SET NOCOUNT ON

DECLARE @vLeadId INT

BEGIN

INSERT INTO LMS_ERROR_LOG

( PROSPECT_ID,METHOD_NAME,EXCEPTION,DATE_TIME )

VALUES

( @pProspectId,@pMethodName,@pErrorMsg,getdate() )

RETURN 0

IF (@@ERROR>0)

GOTO errHandler

errHandler:

RETURN -1

END

GO