Try catch in SQL Server stored procedure with examples

by
Try catch in SQL Server stored procedure

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.
Try catch in SQL Server stored procedure example
Products Table Design
  • 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
Try catch in SQL Server stored procedure
Exception Handled using the TRY-CATCH Block
  • 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
Throw custom exception in SQL Server stored procedure
Custom Exception Thrown
  • 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'
Multiple try catch in stored procedure SQL Server
Error Message due to Duplicate Record
  • 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'
Multiple try catch in SQL Server stored procedure
Error due to NULL Value
  • 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
Try catch throw in SQL Server stored procedure
Custom Error Message
  • 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'
Try catch and transaction in SQL Server stored procedure
The Transaction is Rolled Back
  • 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
How to get error message in SQL Server stored procedure
Error Information

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
SQL Server stored procedure continue execution on error
The Program did not Break
  • 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
stored procedure continue execution on error SQL Server
The Progam Broke itself

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:

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