Skip to content

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 ( 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
begin tran
exec SP (sample variable list)
rollback tran

CREATE PROCEDURE [dbo].[Stored Procedure] (@UserSessionid, @variable1)

	   /*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 @Now = GETDATE()

       IF @@TRANCOUNT = 0
              SET @TranCount = 1

       BEGIN TRY

                     --Your Code goes here

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


					--Logging code going here (traction saving logic)

              IF @TranCount = 1
                  COMMIT TRANSACTION

       END TRY


           IF @TranCount = 1


           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
              EXEC dbo.ExceptionLog_SP @UserSessionid

       SELECT @DBStatus as [Status],@Message as [Message]        

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.

Published inSQL

One Comment

  1. Undeniably consider that which you said. Your favourite reason appeared to be at the web the simplest
    factor to be mindful of. I say to you, I certainly get annoyed while other folks think about
    worries that they plainly do not recognize about. You controlled to
    hit the nail upon the highest and also defined out the whole thing with no need side effect
    , other people could take a signal. Will likely be again to get more.
    Thank you

Leave a Reply

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