Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

Best practices to write stored procedure

Tips to write stored procedure:

1.  Avoid using prefix “sp_” in the stored procedure name:

System stored procedure starts with prefix _sp, If a stored procedure name begins with “SP_,”

Then SQL server first searches in the master database and then in the current session database.

Searching in master database or in system stored procedure causes extra overhead and even a wrong

Result if another stored procedure with the same name is found in master or in system stored procedures.


2.  Include SET NOCOUNT ON statement:

Set the SET NOCOUNT ON option in the beginning of the stored procedure to avoid the unnecessary message like number of rows affected by the SQL Server.

CREATE PROCEDURE [dbo].[StoredProcedureName]
  -- Add the parameters for the stored procedure here
    @xyz dataType
AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   --interfering with SELECT statements.
            SET NOCOUNT ON;
 
--Select statement for result
END


3.  Try to avoid using the temp table in the stored procedure:

When you use the temp table it will do the compilation every time.

 4.  Try to avoid using Select * from:

Avoid using Select * from which selects all columns, if you require specific column then use only specific columns, using specific columns improves performance.

5.  Try to Avoid using Cursor:  Cursor consume more memories, reason it degrade performance instead of Cursor my suggestion is to use T-SQL loop or TVP(Table valued parameter).

6.  Try to avoid using more variables in stored procedure:

--bad example ,avoid using like this
DECLARE @ActiveLatestProductInfo int,
 @ExpiredLatestProductInfo int,
 @ActiveProductInfoCount int,
 @ExpiredProductInfoCount int
 
--Select recent active product info id
SELECT @ActiveLatestProductInfo=MAX(id) FROM Products WHERE productid=@ProductId  AND DeletedDate IS NULL
 
--Select recent expired  product info id
SELECT @ExpiredLatestProductInfo=MAX(id) FROM Products WHERE productid=@ProductId AND DeletedDate IS NOT NULL
 
--Select active product count
SELECT @ActiveProductInfoCount=MAX(id) FROM Products WHERE productid=@ProductId AND DeletedDate IS NULL
 
--Select product count
SELECT @ExpiredProductInfoCount=MAX(id) FROM Products WHERE productid=@ProductId AND DeletedDate IS NOT NULL

 

7.  Use the Try catch:

Use the Try catch statement properly in the stored procedure to handle the errors in the runtime.

BEGIN TRY
--t-sql code
END TRY
BEGIN CATCH
--error handling code
END CATCH


8.  Use proper indexing: I recommend using the proper indexing, unused index degrade performance. Create index only for frequently used columns this will improve performance. Do not create an index on the columns that are not used anywhere in the where clause. It will require an extra roundtrip to query the result.

9.  Use qualified name:

Write stored procedure with full qualified name, Use schema name with object name:

 

 
--Prefere to use
CREATE PROCEDURE [dbo].[GetOrderDetilsByUserId_SP]
.....
 
SELECT columnname1,..column n FROM [dbo].[Order]
 
--Avoid below
 
CREATE PROCEDURE GetOrderDetilsByUserId_SP
.....
 
SELECT columnname1,..column n FROM Orders
 

Invalid entry,please enter valid data.

Loading