Featured, SQL

Exception Handling and Rollback Transaction in SQL Server Stored Procedure

March 10, 2022

As developers, we often encounter exceptions. What do you do when you encounter an exception in Sql Server? You write a stored procedure to handle the exception!
In this article, I will show you how to write a stored procedure that can handle sql server exceptions and rollback the transaction in case of an error / exception.

Consider we are having two tables “Students” & “Events”

Student table consists of records of each student admitted in the school and the events table consists of records of all the transactions happening on the Database.

Example –


INSERT INTO Students(Name, Age, AdmissionDate) VALUES ('Bob', 20, GETDATE())

INSERT INTO Events(EventType, EventDate) VALUES ('Student Added', GETDATE())

As you can see in the Students Table design, the Name column accepts only 8 characters. We will execute the above insert statements again but the Student Name will be more than 8 characters.


INSERT INTO Students(Name, Age, AdmissionDate) VALUES ('Too Long Student Name', 20, GETDATE())

INSERT INTO Events(EventType, EventDate) VALUES ('Student Added', GETDATE())

As you can see, the second insert query has executed successfully, whereas the first insert query failed. The second insert query is depenedent on first insert query. We don’t want the record to be added in the Events table if the first insert query is failing.

Now, we will write a stored procedure, where we will have to execute both these insert queries. And we want to insert “Student Added” record in the Events table only if the insert query on the Students table was successful. And handle any kind of exception occurred in the stored procedure. We will log the exception messages in the Events table.

Note: I will clear the previous test data from both these tables to start fresh


CREATE PROCEDURE AddStudent
	@StudentName VARCHAR(20),
	@Age INT
AS
BEGIN
	SET NOCOUNT ON;
	--Creating a Tranasction. You can give any name to it
	BEGIN TRANSACTION StudentTransaction  

	--We will write our sql queries inside the try catch block for handling exceptions
	BEGIN TRY
		--Inserting record in Student table
		INSERT INTO Students(Name, Age, AdmissionDate) VALUES (@StudentName, @Age, GETDATE())

		--Adding event "Student Added" in the Events table
		INSERT INTO Events(EventType, EventDate) VALUES ('Student Added', GETDATE())

		--Commit the Transaction "StudentTransaction" which we created at the begining
		COMMIT TRANSACTION StudentTransaction
	END TRY
	BEGIN CATCH
		--Rollback the Transaction.
		ROLLBACK TRANSACTION StudentTransaction

		--If "Student Added" records gets inserted upon exception in first insert query, it will be rolled back.

		--Handle Exception and log the exception message in the Events table
		DECLARE @ErrMsg NVARCHAR(500) = (SELECT ERROR_MESSAGE())
		INSERT INTO Events(EventType, EventDate) VALUES (@ErrMsg, GETDATE())

		SELECT @ErrMsg
	END CATCH
END

Let’s execute our stored procedure.