This tutorial is about the try-catch in SQL Server stored procedure with examples. We will discuss some of the examples that will help you understand exception handling more deeply. Also, in the end, we will discuss the best practices of exception handling in stored procedures in SQL Server.
You can also learn more about exception handling in our Exception Handling in SQL Server (Complete Tutorial) article where we have covered all the aspects of exception handling with examples.
- Exception handling in SQL Server stored procedure
- Try catch in SQL Server stored procedure example
- Throw custom exception in SQL Server stored procedure
- SQL Server begin transaction try catch rollback example
- Multiple try catch in SQL Server stored procedure
- Try catch throw in SQL Server stored procedure
- Try catch and transaction in SQL Server stored procedure
- How to get error message in SQL Server stored procedure
- SQL Server stored procedure continue execution on error
- SQL Server stored procedure error handling best practices
Exception handling in SQL Server stored procedure
Exception handling is a mechanism to handle runtime errors gracefully. When a program encounters a runtime error the program terminates itself. In such a scenario, we can use the exception handling mechanism so that the program does not terminate itself, instead, a pre-defined task is performed.
While creating a SQL Server stored procedure, you may write some SQL statements that can result in a runtime error. You may not want your program to break in case any error is encountered.
Therefore, in the stored procedure, you can define a TRY-CATCH block. In this block, you can write the SQL queries that can cause a runtime error and also handle this error without breaking the flow of the program.
If the statements written inside the TRY block throws an error, the control is immediately passed to the CATCH block. In the CATCH block, you can write the code you want to execute if any runtime error is encountered.
To better understand this concept, you can refer to the below sections where we have created multiple examples of exception handling techniques.
Try catch in SQL Server stored procedure example
In this section, you will see how you can use a TRY-CATCH block in a stored procedure in SQL Server to handle an exception. We have created an example for better understanding.
- Consider the following Products table.

- This table does not allow a NULL value to be inserted into the columns except the Rating column.
- This means if we try to insert a NULL value into the table, we will face an error.
- We have created a stored procedure that will insert a record in this Products table.
- The stored procedure will take the values as input parameters and insert those values into the table.
- We will write the INSERT statement inside the try block as it is likely to throw an error if we try to insert a NULL value.
USE [master]
GO
CREATE PROCEDURE [dbo].[SPProducts] @ID int, @Name nchar(30)
, @Price real, @Rating int=0
AS
BEGIN
BEGIN TRY
INSERT INTO dbo.Products([Product ID], Name, Price, Rating)
VALUES(@ID, @Name, @Price, @Rating)
END TRY
BEGIN CATCH
PRINT('Error! Cannot Insert a NULL Value into the "Products" Table')
END CATCH
END
- Now we will execute this stored procedure with some NULL values.
USE master
GO
EXEC dbo.SPProducts NULL, NULL, NULL, 10

- As you can see, the code inside the CATCH block got executed and our message is printed on the output screen. Also, the row is not inserted.
- This time, we will execute the stored procedure with the some valid values.
USE master
GO
EXEC dbo.SPProducts 1250 , 'Petroleum Jelly', 80, 9
- This time the record will be inserted successfully in the Products table.
Thus, with the above example, you might have learned how you can use the TRY-CATCH block to handle the exceptions in SQL Server.
Read SQL Server stored procedure parameters
Throw custom exception in SQL Server stored procedure
We can throw custom exceptions to customize the error information that is more useful to the user and is more specific. Thus, it is easy for the user to recognize the error and its cause.
Also, many times in our SQL program, we do not want a part of SQL code to run in a certain condition. Therefore, we throw a custom exception in that condition, so that the control passes to the CATCH block and we handle that situation differently. In this section, you will learn how you can throw custom exceptions in SQL Server stored procedures with an example.
To throw a custom exception in SQL Server stored procedure, you have to use the THROW statement inside the stored procedure. When you use the THROW statement, you are supposed to provide three things: The custom error number, error message, and error state. However, you can even throw an error without providing this information.
- We have created the following stored procedure that will insert a record in the Phones table:
USE master
GO
ALTER PROCEDURE dbo.ThrowException @Phone nchar(20), @Fname nchar(10), @Lname nchar(10)
AS
BEGIN
BEGIN TRY
IF @Phone IS NULL OR @Fname IS NULL OR @Lname IS NULL
THROW 50010, 'Null Value(s) passed. Cannot Insert the Record', 1
ELSE
INSERT INTO dbo.Phones(Phone, [First Name], [Last Name])
VALUES( @Phone, @Fname, @Lname)
END TRY
BEGIN CATCH
PRINT(ERROR_MESSAGE())
END CATCH
END
- Executing the stored procedure and passing NULL values as parameters should throw an exception:
USE master
EXEC dbo.ThrowException NULL, NULL, NULL

- Our custom message is displayed on the screen.
In this way, we can define our own custom information about the exception instead of the system-defined information using the THROW statement.
Read SQL Server stored procedure return value
SQL Server begin transaction try catch rollback example
Multiple try catch in SQL Server stored procedure
There can be some SQL statements that can cause any error in a stored procedure. To handle every error separately, you can also use multiple try-catch blocks in a SQL Server stored procedure. This helps to get more specific information in case of errors rather than putting all the error-prone statements within a single Try-Catch block. Let us understand this with an example.
- We have a table named Login containing the login credential of users.
- This table has two columns: Username and Password
- The column Username is the primary key column.
- We will create a stored procedure to insert a new user into this table.
- Firstly, we will verify if the record is already stored in the table or not.
- We will only insert the new record if it is already not stored in the table.
USE master
GO
CREATE PROCEDURE SPNewLogin @User nchar(20), @Pass nchar(20)
AS
BEGIN
BEGIN TRY
IF EXISTS(SELECT * FROM dbo.Login WHERE Username= @User)
THROW 51002, 'User ID Already Exists in the Table. Cannot Insert', 1
ELSE
BEGIN TRY
INSERT INTO dbo.Login( Username, Password)
VALUES( @User, @Pass)
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS Error
END CATCH
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS Error
END CATCH
END
- The above code seems to be complex, but it is easy once you try to understand it.
- We have used two Try Catch blocks i.e one inside the other.
- One Try Catch block created to handle the custom exception thrown in case we are trying to insert an exisitng record into the table.
- The other Try Catch block is created to handle the exception if any error arises while inserting the record into the table.
- Let us try to insert an existing record and see what message we see when exception is thrown:
USE master
GO
EXEC SPNewLogin 'David', 'David1998'

- As you can see the desired Try Catch block got executed and displayed the error message accordingly.
- Now let us try to insert a record with a NULL value. As we mentioned above, the Username column is a Primary Key column and will not accept NULL values.
USE master
GO
EXEC SPNewLogin NULL, 'David1998'

- As you can see, the desired Catch block got executed and the desired error message is displayed on the output screen.
Thus, in this way you can use multiple Try-Catch blocks in a nested manner.
Read SQL Server stored procedure vs function
Try catch throw in SQL Server stored procedure
In this section, you will learn how you can throw a custom exception and handle it with a Try-Catch block in sql server stored procedure. We will show an example where we will use this approach.
- We have a table named Employees storing the Employee Details.
- Another table is the Department table containing department details.
- When we insert a record in Employees table, the EmpDep column should only accept the values that are in the Department table i.e. the department should be a valid department.
- We have created a stored procedure that will insert a record in the Employees table.
- But first, it will check if the department ID is valid or not.
USE master
GO
CREATE PROCEDURE dbo.SPEmployees @EmpID int, @EmpName nchar(30), @EmpDep int
AS
BEGIN
BEGIN TRY
IF EXISTS(SELECT * FROM dbo.Department WHERE DepID= @EmpDep)
INSERT INTO dbo.Employees( EmpID, EmpName, DepID)
VALUES(@EmpID, @EmpName, @EmpDep)
ELSE
THROW 51002, 'Not a Valid Department', 1
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS ERROR
END CATCH
END
- If the department ID is valid then the record will be inserted. Otherewise a user defined exception will be thrown.
- Let us try to insert a record with an invalid department ID.
USE master
GO
EXEC dbo.SPEmployees 1012, 'Albert', 18

- The exception is thrown and our custom error message is displayed.
Thus, you might have learned how you can use the THROW statement with a Try-Catch block in a SQL Server stored procedure.
Try catch and transaction in SQL Server stored procedure
We can use the Try-Catch block with transactions in SQL Server to handle the errors encountered gracefully. We have created an example where we will demonstrate the use of the Try-Catch block in a transaction.
- We have created a stored procedure that will insert a record in the Persons table and a record in the Phones table.
- The first Insert statement is to insert into the Phones table and the second Insert statement is for the Persons table.
USE [master]
GO
CREATE PROCEDURE [dbo].[InsertIntoPersons] @FirstName nchar(10), @LastName nchar(10),
@Age smallint, @Gender nchar(7), @Email nchar(30)='N/A', @Phone nchar(20)= 'N/A', @Location nchar(20)
AS
BEGIN
BEGIN TRANSACTION
BEGIN TRY
INSERT INTO dbo.Phones( Phone, [First Name], [Last Name])
VALUES( @Phone, @FirstName, @LastName)
INSERT INTO dbo.Persons(
[First Name], [Last Name], Age, Gender, Email, Phone, Location)
VALUES( @FirstName, @LastName, @Age, @Gender, @Email, @Phone, @Location)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END
- We want that if any error is occured while inserting into the Persons table, the record should not be inserted into the Phones table also.
- We will begin the transaction. If both the Insert statements are executed without any error, the transaction will be commited.
- Otherwise, if any error occurred while inserting into the second table, the transaction will be rolled back.
- Now let us try to execute this stored procedure.
- The Email column of the Persons takes does not allow NULL values.
- We will try to insert a NULL value in the Persons table.
USE master
GO
EXEC dbo.InsertIntoPersons 'Kate', 'Lincon', 45, 'Female', NULL, '+1 540 034 735', 'Reston'

- If we check both the tables, the records are not inserted. This means the transaction is rolled back.
Thus, you might have learned how you can use the Try-Catch block in a SQL Server stored procedure for managing transactions.
Read SQL Server stored procedure insert into with examples
How to get error message in SQL Server stored procedure
In this section, you will learn how you can get the error message in SQL Server stored procedure.
In SQL Server, you have some predefined functions that store information about the exception that has occurred recently. Some of these are:
- ERROR_MESSAGE()
- ERROR_NUMBER()
- ERROR_SEVERITY()
- ERROR_STATE()
- ERROR_PROCEDURE()
- ERROR_LINE()
You can use these functions to retrieve the information about the exception and display it to the user in a customized way. Let us see an example.
- The following stored procedure finds division of two numbers.
- If we divide a number by zero, the system will throw an exception.
- To get the error message and other information about the exception, you can use the functions defined above.
USE master
GO
CREATE PROCEDURE dbo.DivideZero @Num1 real, @Num2 real
AS
BEGIN
DECLARE
@Result real
BEGIN TRY
SET @Result= @Num1 / @Num2
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END
- Let us try to divide a number by zero:
USE master
GO
EXEC dbo.DivideZero 10, 0

Thus, you might have learned how you can get the error message in a SQL Server Stored Procedure.
Read SQL Server stored procedure naming convention and best practices
SQL Server stored procedure continue execution on error
When we encounter any error in a SQL Server stored procedure during execution, the execution is supposed to be stopped. However, you may have noticed that the execution did not stop.
This is because the XACT_ABORT option is set to OFF by default. When this option is OFF, only the transaction that is resulting in an error will be rolled back and the rest of the transactions in the batch are not affected. For example:
- Consider the following stored procedure to divide two numbers.
USE [master]
GO
CREATE PROCEDURE [dbo].[DivideZero] @Num1 real, @Num2 real
AS
BEGIN
DECLARE
@Result real
SET @Result= @Num1 / @Num2
PRINT(@Result)
PRINT('Statement After the error')
END
- If we try to divide a number by zero, the program is supposed to break.
USE master
GO
EXEC dbo.DivideZero 10, 0

- But, it is not so. The program did not break and the PRINT statement after the error causing statement got executed.
You can manually set the XACT_ABORT option to ON to avoid this behavior. But, you might think why we should turn it ON. Because in the OFF condition, our program is not terminated and we did not need any error handling mechanism.
In data modification statements, this option must be set to ON. Also, when the error severity level is high, you should not set it to OFF. You can manually turn it ON as:
SET XACT_ABORT ON;
When this option is set to ON, all the transactions after the error-causing statement are rolled back. For Example:
- Alter the above stored procedure and set the XACT_ABORT to ON.
USE [master]
GO
ALTER PROCEDURE [dbo].[DivideZero] @Num1 real, @Num2 real
AS
BEGIN
SET XACT_ABORT ON;
DECLARE
@Result real
SET @Result= @Num1 / @Num2
PRINT(@Result)
PRINT('Statement After the error')
END
- Now if we try to divide a number by zero, the PRINT statement after the error will not be executed.
USE master
GO
EXEC dbo.DivideZero 10, 0

Thus, you might have known why a stored procedure continues to execute even after an error is encountered.
Read SQL Server stored procedure if else
SQL Server stored procedure error handling best practices
In this section, you will learn the best and recommended ways of exception handling in SQL server 2019. The following are the points that describe the best practices of SQL Server error handling:
- Always recognize the SQL statements properly that can cause an error and put them into the Try-Catch block.
- Do not use exception handling for very simple conditions. If an error can be avoided with an IF-ELSE statement, prefer not to use the Throw statement. Instead, use the IF-ELSE statement.
- Create a log table for storing the errors generated by the stored procedures. This makes it easy to deubg the errors later.
- Create a generic stored procedure for handling different errors. For example, the error information like message, state, etc can be used to make a log table and a common stored procedure for error handling can be called from a Catch block to store the information.
These are some best practices of error handling when you work with stored procedures.
You may like the following sql server tutorials:
- How to view stored procedure in SQL Server
- SQL Server select from stored procedure
- SQL Server stored procedure output parameter
- Stored procedure for search functionality in SQL Server
- Msg 567: Unable to retrieve data for this section of the report
In this tutorial, we learned about, Try catch in SQL Server stored procedure.
- Exception handling in SQL Server stored procedure
- Try catch in SQL Server stored procedure example
- Throw custom exception in SQL Server stored procedure
- SQL Server begin transaction try catch rollback example
- Multiple try catch in SQL Server stored procedure
- Try catch throw in SQL Server stored procedure
- Try catch and transaction in SQL Server stored procedure
- How to get error message in SQL Server stored procedure
- SQL Server stored procedure continue execution on error
- SQL Server stored procedure error handling best practices
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.