How to use Transaction and Try Catch in Stored Procedure

Suppose you are working on multiple table in a database eg updating, deleting and inserting on multiple table then it is always a good practice to maintain Transaction property as using this will undergo changes in all the tables or none. Here is a simple example to implement that.

CREATE PROCEDURE TEST

AS

BEGIN
BEGIN TRY
BEGIN TRANSACTION

----------Write Your Queries here

--------write this in the end of your procedure
DECLARE @ERROR INT
SET @ERROR = @@ERROR
--------@@ERROR is a global variable which will return a non- zero when any error occurs in a procedure

IF @ERROR <> 0
BEGIN
GOTO LOGERROR
END


GOTO PROCEDUREEND

LOGERROR:
DECLARE @ERRORMESSAGE NVARCHAR(MAX)
SELECT @ERRORMESSAGE = [description] FROM master.dbo.sysmessages
where error = @error
--------There is a table in master database which contains records of all errors

INSERT INTO ERRORLOG(ERRORDATE,ERRORSOURCE,ERRORMESSAGE,@ERRORCODE)
VALUES(GETDATE(),'PROCEDURE NAME',@ERRORMESSAGE,@ERROR)
--------If you want to log errors then create a table in your database with the above structure

PROCEDUREEND:

COMMIT TRANSACTION
--------This will complete the transaction

END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
--------This will rollback the transaction

END CATCH

END