SQL Server 2005 Stored Procedure Coding Guidelines and Best Practices
- Write SET NOCOUNT ON in top of the procedure
- Every SELECT statement should return with WITH NOLOCK key word.
- Procedure should be rerunnable, Use IF EXISTS statement …
- procedure always should have return value or return parameter
- Use output parameter wherever necessary
- Each code (for loop, If else etc) should be in BEGIN .. END block. For conditional checks( if else), please follow the same.
- 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
- 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.
- Do not use reserved words, if you can’t avoid the use square brackets like: [event]
- Do not use ‘SP_’ or ‘SYS_’ as a prefix in the stored procedure name.
- 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.
- 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.
- 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.
- 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() )
- 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
@Fname nvarchar(100),
@Mname nvarchar(100)
I normally follow below guidelines too for consitancy
- Stored procedure name should be in CAPS latters for consistancy
- Do Proper indenting ( 2 tabs for start and same for next levels)
- Stored procedure name should follow some standrad to identify as below PROJECT_NAME_<SELECT OR INSERT OR UPDATE OR DELETE>_<OPERATION or TABLE_NAME>
- Global parameter start with "p" and local parameter start with "v" . and for OUT parameter name should @pOut<ParameterName>
- Created By/Date and purpose must be in the procedure as comment
- 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
