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.
Inserting Student Name less than 8 characters
Result:
As the student name was less than 8 characters, our stored procedure executed successfully without any exception.
Inserting Student Name more than 8 characters
As expected, we recieved an exception, because the Student Name was more than 8 characters.
Let’s execute the select queries. We are expecting only the exception message should be inserted into the Events table.
Result:
As expected, this time only the exception message was inserted into the Events table.