Standard SQL Stored Procedure Template

Many developers fail to understand that the code they develop must one day be handled by a maintenance team. So it is highly important to include a few things such that the Stored Procedure (

Many developers fail to understand that the code they develop must one day be handled by a maintenance team. So it is highly important to include a few things such that the Stored Procedure ( SP) would be easy to read and debug.

Over the years, of my research, I find the below design to work the best for me.

/* 
Description: Explain what this SP does and where it is called from
Param: what are the inputs to this sp 
Return: what is a output of this sp
Change History: Please find below the change history of this Stored Procedure 
==================================================================================================
S#  DATE          ID          NAME                   SIGNATURE			REMARKS                 
==================================================================================================   
3  07/03/2018   employid      Tejaswi Pandava        TP2018JULY07       what changes you are doing
==================================================================================================
Example: 
begin tran
exec SP (sample variable list)
rollback tran

*/
CREATE PROCEDURE [dbo].[Stored Procedure] (@UserSessionid, @variable1)
AS
BEGIN

	   /*Declaration Variable Section*/
       DECLARE @TranCount BIT,
                     @Error TINYINT,
                     @ErrorMessage VARCHAR(500),
                     @ErrorSource VARCHAR(100),
                     @ErrorStack VARCHAR(1000),
                     @Now DATETIME,
                     @DBStatus BIT,/*1 in Try Block(sucess),
								     0 in catch block(failure) */
                     @Message VARCHAR(1000),


 	   /*Declaration Variable Section*/
       DECLARE @exampletable TABLE('arguments')

 
       SET NOCOUNT ON
       SET @Now = GETDATE()

       IF @@TRANCOUNT = 0
       BEGIN
              SET @TranCount = 1
              BEGIN TRANSACTION
       END


       BEGIN TRY
              BEGIN

                     --Your Code goes here

                     SET @DBStatus = 1
                     SET @Message = ('what you want to return')

              END


					--Logging code going here (traction saving logic)


              IF @TranCount = 1
              BEGIN
                  COMMIT TRANSACTION
              END

       END TRY


       BEGIN CATCH

           IF @TranCount = 1
			ROLLBACK TRANSACTION

 

           SET @Error = 1;
           SET @DBStatus = 0
           SET @Message = 'failed to save'
           SELECT @ErrorMessage = CONVERT(VARCHAR(25), ERROR_NUMBER()) +
		    '-' + ERROR_MESSAGE(),
                  @ErrorSource = ERROR_PROCEDURE(),
                  @Now = GETDATE(),
                  @ErrorStack = 'Line No:' + CONVERT(VARCHAR(25), ERROR_LINE())
       END CATCH

      

       /*Logging error section */
       IF @Error = 1
       BEGIN
              EXEC dbo.ExceptionLog_SP @UserSessionid
                     ,@ErrorSource
                     ,@ErrorMessage
                     ,@ErrorStack
                     ,NULL
                     ,@ErrorMessage
                     ,@Now
       END

       SELECT @DBStatus as [Status],@Message as [Message]        
       SET NOCOUNT OFF
END

Let’s see why this works for me


1. Capturing the description, change history and example

Now let us suppose we are debugging and came across an SP name in SQL object explorer. Normally we would wonder what this SP does and where is it used. If a description is written then we would understand at once. Now if we want to know whether anything has been modified in these recently time then we can see that in the local change history. No need of going to TFS/GIT to check the changes.
This process saves a lot of time for our maintenance team.

2. Declaration of variables and tables at one location

It increases readability and simplifies your SP. Also making sure to follow a proper naming convention for variables and tables, will help in understand what the variable does.

3. Is your SP a Insert/Delete/Update SP or Select SP?

If your SP programmed to perform insert, update, deleted. Enclosing the code in a transaction block is mandatory, why you ask?
Let us suppose you have 5 update statements and the 5th update statement has some issues. Without transactions, your SP will be updating the 4 and on encountering the 5th it will fail. So with a transaction, you can avoid this. If any update fails you can use catch block to catch the error and use rollback to rollback all transactions.

Now if your SP is programmed to perform a SELECT operation then replace the transactions with return statements (not the try and catch block as you may still encounter errors due to data) as this has no use here.

4. Error logging?

So error logging has 2 steps reading and logging, for reading we would use the system-defined function and for logging, We use an SP (ExceptionLog_SP) to log to a table. While logging, we store line number, stack, message, userid and error location.

Leave a Reply

Your email address will not be published. Required fields are marked *