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.
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.
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.
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.