Best Practice for Store Procedure

Best Practice 1:

Name of store procedure should not start with SP_

Cause:

SQL server uses prefix SP_ for system store procedures which are in sys schema of Master database.

E.g:- SP_WHO, SP_HELP etc. 

If we create any user defined store procedure sql server will first search in Master database then in our current working directory. 

Let's assume our current database is Exact_Help. Now we are creating store procedure named SP_WHO'

USE Exact_Help

CREATE PROCEDURE SP_WHO

AS

BEGIN

   

    SELECT 'Exact Help'

   

END

Now we are executing this procedure:

EXECUTE SP_WHO

Sample output:

spid	ecid	status	loginame	hostname	blk	dbname	cmd	request_id
1	0	background	sa		0	NULL	RESOURCE MONITOR	0
2	0	background	sa		0	NULL	XE DISPATCHER	0
3	0	background	sa		0	NULL	XE TIMER	0
4	0	background	sa		0	NULL	LAZY WRITER	0
5	0	background	sa		0	NULL	LOG WRITER	0
6	0	background	sa		0	NULL	LOCK MONITOR	0
7	0	background	sa		0	master	SIGNAL HANDLER	0

It is not executing our procedure!!! It is executing Master.sys.SP_WHO

If we will execute:

EXECUTE EXACT_HELP.dbo.SP_WHO

Event that it will execute Master.sys.SP_WHO

Problems if user defines procedures begin with prefix sp_:
a. It will take extra execution time to search the stored procedure in sys schema of Master database.
b. If this procedure is already a system stored procedure we will get unexpected result.

Best Practice 2: 

We should keep the NOCOUNT setting on in the stored procedure.

Cause: 

In any application stored is mostly executed by program where there is no need to know the total numbers of affected rows by SELECT, INSERT, UPDATE, DELETE, MARGE etc statements. I am explaining it by an example. We are creating a stored procedure named usp_GetMessage:

CREATE PROCEDURE usp_GetMessage

AS

BEGIN

   

    SELECT * FROM sys.messages

        

END

Now we are executing this procedure:

EXECUTE usp_GetMessage

If we will click on the Messages tab of query result grid will find a message something like:

(97526 row(s) affected)

Sql server has to pay extra cost to calculate total numbers of affected rows by this procedure. But if we will set the nocount on that is:

ALTER PROCEDURE usp_GetMessage

AS

BEGIN

   

    SET NOCOUNT ON

   

    SELECT * FROM sys.messages

   

END

Now if will execute the procedure sql server will no count the total affected rows by this procedure and we can decrease the total execution time of procedure in each execution.

Note:

There is no relation of NOCOUNT setting and @@ROWCOUNT function.

Best Practice 3:

Never update the value of parameters of a store procedure before using in sql queries.

Cause

I am explaining it by an example. Creating following objects in sql server

--Creating a table

CREATE TABLE tblMessage (

    ntMessageID BIGINT IDENTITY PRIMARY KEY,

    ntSeverity INT,

    vcMessage VARCHAR(500)

)

--Inserting some records

INSERT INTO tblMessage

SELECT

    Severity,

    LEFT ([TEXT], 500)

FROM Sys.messages

--Creating a non clustered index on it

CREATE NONCLUSTERED INDEX NCI_Severity

ON tblMessage(ntSeverity)

INCLUDE(vcMessage)

GO

--Creating stored procedure

CREATE PROC uspGetMsg1(

     @ntSeverity AS INT

)

AS

    

     SET @ntSeverity = @ntSeverity - 4

         

     SELECT vcMessage

     FROM tblMessage

     WHERE ntSeverity = @ntSeverity

GO

    

--Creating similar stored procedure

CREATE PROC uspGetMsg2(

     @ntSeverity AS INT

)

AS

             

     SELECT vcMessage

     FROM tblMessage

     WHERE ntSeverity = @ntSeverity

Store procedure uspGetMsg1 and uspGetMsg2 is exactly same expect strore procedure usoGetMsg1 modified the parameters. Now we are going to excecute the store procedure uspGetMsg1 and uspGetMsg2:

EXECUTE uspGetMsg1 16  

EXECUTE uspGetMsg2 12

In the procedure uspGetMsg1:

@ntSeverity = @ntSeverity - 4

ntSeverity = 16-4 = 2

So both stored procedure will get the data from tblMessage of same ntSeverity that is 12. Now we are comparing the execution plan:

For same ntSeverity we are getting different execution cost. Why?
Stored procedure uspGetMsg1 has got compiled for ntSeverity = 16 not for the updated value of ntSeverity. You can check it by right clicking on SELECT operator in the execution plan checking parameter list in the properties:

 

It shows the procedure has compiled for @ntSeverity = 16 but while actual value is 12. So execution plan is displaying the query cost for ntSeverity = 16 not for 12. If we will observe the tblMessage we will find this table has around 69839 records for ntSeverity = 16 while three are only around 11 records for ntSeverity = 12 that why query 1 is costlier than query 2

Conclusion: 

Sql server is not able to complie the query with updated value of procedure parameters which may lead to wrong execution plan. So do not update the procures parameters before using the queries.

Best Practice 4: 

Don't write such stored procedure which has big difference in execution plan due to change of value of parameters.

Best Practice 5: 

Use TRY CATCH block to handle the errors.
I am explaining it one example. Let's assume in our current database has three tables of following schema:

CREATE TABLE tblEmp(

     ntEmpID  BIGINT PRIMARY KEY IDENTITY,

     vcName  VARCHAR(10)

)

CREATE TABLE tblDep(

     ntDepID  BIGINT PRIMARY KEY IDENTITY,

     vcName  VARCHAR(20)

)

CREATE TABLE tblOrder(

     ntOrderID  BIGINT PRIMARY KEY IDENTITY,

     vcName  VARCHAR(10)

)

Now we are creating a store procedure:

CREATE PROC uspInserData

AS

BEGIN

     BEGIN TRY

    

          INSERT INTO tblEmp VALUES('Scott'),('Greg')

          INSERT INTO tblDep VALUES('Information Thechnoloy'),('Electrical')

          INSERT INTO tblOrder VALUES('Book'),('Copy')

         

     END TRY

     BEGIN CATCH

         

          SELECT

              ERROR_MESSAGE() AS ErrMsg,

              ERROR_LINE() AS ErrLine,

              ERROR_PROCEDURE() AS ErrProc

                

     END CATCH

END

Now we are going to execute this store procedure:

 EXECUTE dbo.uspInserData

We will get error message like:

ErrMsg	ErrLine	ErrProc
String or binary data would be truncated.	7	uspInserData

In the catch block we can handle message according to our requirements. This error message doesn't tell which table has thrown this error message. With help of Error line number we can find it. Line number:
1. Always start from CREATE or ALTER PROC
2. Error line number also includes empty line
3. Since dynamic sql executes in different scope so error line in case of error in dynamic query start from beginning of the dynamic query.

Best Practice 7: 

Follow coding convention.

Best Practice 8: 

Deallocate the temporary tables, sql cursors if it has no use.

Cause: 

Temporary tables and sql cursors are create in the tempdb database. Disk space etc of system database tempdb is very crucial for query performance so we should use resources of tempdb when it is really necessary. For example:

CREATE uspTempdbManagemet   

AS

BEGIN

     DECLARE @ntSeverity AS INT = 0

     DECLARE @ntCount AS INT

    

     SELECT severity,COUNT(*) AS ntCount

     INTO #Tempdb

     FROM sys.messages

     GROUP BY severity

    

     UPDATE tblEmployee SET ntAge = ntAge + 1

     SELECT * FROM tblEmployee

    

    

     WHILE(EXISTS(SELECT * FROM #Tempdb WHERE severity >@ntSeverity)) BEGIN

    

          SELECT

              @ntSeverity = severity,

              @ntCount = ntCount

          FROM #Tempdb

          WHERE severity <> @ntSeverity

          ORDER BY severity

    

          EXECUTE dbo.uspMsg @ntSeverity,@ntCount

         

          SET @ntSeverity = @ntSeverity + 1

         

     END

    

     SELECT * FROM tblOrder

     SELECT * FROM tblOrderHistory

    

END

Above stored procedure has few problems from use of temporary table point of view. We should:

a. Create temporary table when it is necessary
b. Drop temporary table when it is not necessary

Now we are writing same stored procedure in better way:

CREATE uspTempdbManagemet   

AS

BEGIN

     DECLARE @ntSeverity AS INT = 0

     DECLARE @ntCount AS INT

    

     UPDATE tblEmployee SET ntAge = ntAge + 1

     SELECT * FROM tblEmployee

    

    

     SELECT severity,COUNT(*) AS ntCount

     INTO #Tempdb

     FROM sys.messages

     GROUP BY severity

    

     WHILE(EXISTS(SELECT * FROM #Tempdb WHERE severity >@ntSeverity)) BEGIN

    

          SELECT

              @ntSeverity = severity,

              @ntCount = ntCount

          FROM #Tempdb

          WHERE severity <> @ntSeverity

          ORDER BY severity

    

          EXECUTE dbo.uspMsg @ntSeverity,@ntCount

         

          SET @ntSeverity = @ntSeverity + 1

         

     END

    

     DROP TABLE #Tempdb

    

     SELECT * FROM tblOrder

     SELECT * FROM tblOrderHistory

    

END

Same is true for other temporary objects like sql cursor etc.

Best Practice 9: 

Avoid using global temporary table in the stored procedure.

Cause: 

Scope of global temporary tables is connection. For example:

CREATE PROC uspGlobalTempTable

AS

     SELECT *

     INTO ##tblGlobal

     FROM sys.messages

    

     WAITFOR DELAY '0:00:30';

     DROP TABLE ##tblGlobal 

Now execute this store procedure in two query window of Sql server management studio (ssms).

Query window 1:
EXECUTE dbo.uspGlobalTempTable

Query window 2:
EXECUTE dbo.uspGlobalTempTable

We may get error message like:
There is already an object named '##tblGlobal' in the database.

So try to avoid using global temporary tables in the store procedures.

Best Practice 10: 

Use schema name while executing any stored procedure.

Cause: 

If we will not specify the schema name in the stored procedure first it will search in sys schema then default schema of user. For example:

Let's assume any user has dbo as default schema. He has created stored procedure in his default schema:

CREATE PROC XP_LOGININFO

AS

     SELECT DB_NAME()

Now he is executing this stored procedure:

EXECUTE XP_LOGININFO    

He is expecting that result set will be current database name while he got something like this:

account name	type	privilege	mapped login name	permission path
NT AUTHORITY\SYSTEM	user	admin	NT AUTHORITY\SYSTEM	NULL
NT SERVICE\MSSQL$SQLEXPRESS	group	admin	NT SERVICE\MSSQL$SQLEXPRESS	NULL
Ritesh-PC\Ritesh	user	admin	Ritesh-PC\Ritesh	NULL
BUILTIN\Users	group	user	BUILTIN\Users	NULL
RITESH-PC\ExactHelp	user	user	RITESH-PC\ExactHelp	NULL

Sql server has first searched this procedure in the sys schema and it found this procedure at there and executed it and it didn't executed the stored procedure created by him. 

So correct and efficient way is to execute any stored procedure is specify the schema name. For example:

EXECUTE dbo.XP_LOGININFO