Exception Handling in SQL Server (Complete tutorial)

In this tutorial, we will learn about exception handling in SQL Server 2019. We will learn about the exceptions, how and why the exceptions arise, and how to mitigate them.

• What is Exception handling in SQL Server?
• Types of exceptions in SQL Server
• Try Catch in SQL Server
• Exception handling in SQL Server stored procedure
• Try catch in SQL Server stored procedure example
• Throw a custom exception in SQL Server stored procedure
• Throw a custom exception in SQL Server stored procedure example
• Exception handling in SQL server function
• SQL Server stored procedure error handling best practices

For all the examples, I have used sql server management studio and sql server 2019 express edition.

What is Exception handling in SQL Server?

Some errors are generated by the application at runtime. These errors can be generated because of various reasons and the application is not able to handle these errors, causing the program to break and throw an exception.

Usually, these are generated because of unexpected human input such as passing an unexpected data type input e.g. passing an integer at the place of a string value, dividing a number by zero, etc.

But in real-life scenarios, the program is not supposed to be broken down, but execute flawlessly. Therefore, there is a need for a mechanism that can handle the situations that affect the program’s normal execution or cause it to break. This mechanism is called exception handling or error handling.

SQL Server also provides exception handling mechanism like any other programming language. There are different ways to handle the exceptions. We will discuss these in the upcoming sections with the help of examples.

• Consider the following program which divides the two numbers:

USE [master]
GO
DECLARE
	@Number1 int,
	@Number2 int,
	@Result real

SET @Number1=10
SET @Number2=0

SET @Result=@Number1/@Number2
PRINT(@Result)

• We know that dividing a number by 0 results in infinity. But let us see how the program executes it:

• Observe the above image. The program threw an exception with the message as ‘Divide by zero error encountered.’
• You can also notice that no result was printed.
• Imagine a real-life scenario, where you encountered such an exception and that exception caused your program to break. Therefore, to avoid such conditions, we use exception handling.

Read: Advanced Stored Procedure Examples in SQL Server

Types of exceptions in SQL Server

There are two types of exceptions in SQL Server. These are:

1. System Defined Exceptions
2. User-Defined Exceptions

System Defined Exceptions: These are the exceptions that are generated by the system during the program execution. The error messages are predefined in the system and the final user will see the predefined message on the console.

User-Defined Exceptions: These are the exceptions that are thrown explicitly by the programmer. The programmer can raise exceptions at any part of the SQL code according to the logic he has implemented in the program.

The programmer can also create custom messages that he finds will be interactive and meaningful to the user. He can also store these errors into log tables to maintain a log of generated exceptions.

Try Catch in SQL Server

In this section, we will learn how we use the Try-Catch block in SQL Server to handle exceptions. In SQL Server we use BEGIN TRY and BEGIN CATCH blocks to handle exceptions.

• We put the SQL statements that may cause an exception in the BEGIN TRY block.
• If any exception arises, the control is immediately transferred to the BEGIN CATCH block.
• In the BEGIN CATCH block, we write the statements that should be executed if an exception is occurred in the BEGIN TRY block. That means the statements inside the BEGIN CATCH block are used for handling the flow of the program in case the exception is triggered.
• If no exception arises i.e. all the SQL statements inside the BEGIN TRY block are executed, the control is not transferred to the BEGIN CATCH block.

Now let us see the general syntax for BEGIN TRY and BEGIN CATCH block.

BEGIN TRY
--SQL Statements where exception may encounter
END TRY

BEGIN CATCH
--SQL statements to handle exceptions
END CATCH

• Now consider the above example i.e. the program to divide two numbers.
• The above program can be re-written using exception handling as:

USE [master]
GO
DECLARE
	@Number1 int,
	@Number2 int,
	@Result real

SET @Number1=10
SET @Number2=0
BEGIN TRY
	SET @Result=@Number1/@Number2
	PRINT(@Result)
END TRY
BEGIN CATCH
	PRINT('You cannot divide a number by zero')
END CATCH

• Now execute the above program. You will notice that this time also the result does not get printed. But, the statement inside the BEGIN CATCH block got executed.
• Also, try to change the value from 0 to another integer value. This time you will see the expected result.

Exception handling in SQL Server stored procedure

Sometimes we have some statements inside a stored procedure that can generate run-time errors and causing the application to break. These can occur while inserting data into a table, updating data of a table, parsing input parameters etc.

In such situations, we can use exception handling in SQL Server stored procedure. In this section, we will understand how we can implement exception handling in stored procedures in SQL Server. We will understand this with an example in SQL Server 2019.

• Let us consider the following example of a stored procedure that inserts a record into the Login table.

USE [master]
GO
CREATE PROCEDURE InsertRecord @Username nchar(20), @Password nchar(20)
AS
BEGIN
	INSERT INTO [dbo].[Login](Username, Password)
	VALUES(@Username, @Password)
END

• The Username column is set as Primary Key in the Login table. This means that if we try to insert duplicate value in the Username column, we should get an error.
• Try to insert duplicate value in this column by executing the procedure as:

USE [master]
GO
EXEC InsertRecord 'Brock', 'Brock@123'

• As a result, we encountered an exception and the program broke.
• Now let us alter this stored procedure. This time write the stored procedure with the exception handling mechanism.

USE [master]
GO
ALTER PROCEDURE InsertRecord @Username nchar(20), @Password nchar(20)
AS
BEGIN
	BEGIN TRY
		INSERT INTO [dbo].[Login](Username, Password)
		VALUES(@Username, @Password)
	END TRY

	BEGIN CATCH
		PRINT('The username is already taken. Try another one')
	END CATCH
END

• Exceute the stored procedure again with a duplicate value in the Username column.

USE [master]
GO
EXEC InsertRecord 'Brock', 'Brock@123'

• This time you can see that we did not get any error and our custom message is displayed.

Thus, you might have learned how we can implement exception handling inside a stored procedure in SQL Server. You can try some more examples. We will also discuss some more examples in this article.

Try catch in SQL Server stored procedure example

In this section, we will see another example of a stored procedure with an exception handling mechanism implemented. We will see how we can use Try-Catch in SQL Server stored procedure.

• Consider the following stored procedure to print the sum of two numbers:

USE [master]
GO
CREATE PROCEDURE PrintSum @Number1 int, @Number2 int
AS
BEGIN
	DECLARE
		@Result int
	SET @Result=@Number1+@Number2
	PRINT('Sum is:'+@Result)
END

• Everything is looking fine. However, it is not so. Execute the procedure with the following query:

USE [master]
GO
EXEC PrintSum 5, 7

• You will encounter an exception. This is because when we use the PRINT statement to print a string along with a numeric value, we need to change the numeric value into a string data type before concatenation.
• Now to handle this exception, we will use BEGIN TRY-BEGIN CATCH block.
• Put the error-causing statement inside the BEGIN TRY block.
• Write statements for handling the exception inside the BEGIN CATCH block.

USE [master]
GO
ALTER PROCEDURE PrintSum @Number1 int, @Number2 int
AS
BEGIN
	DECLARE
		@Result int
	SET @Result=@Number1+@Number2
	BEGIN TRY
		PRINT('Sum is:'+@Result)
	END TRY
	BEGIN CATCH
		SELECT  
			 ERROR_NUMBER() AS [Error Number]  
			,ERROR_SEVERITY() AS [Error Severity]  
			,ERROR_STATE() AS [Error State]  
			,ERROR_PROCEDURE() AS [Error Procedure]  
			,ERROR_MESSAGE() AS [Error Message]
			,ERROR_LINE() AS [Error Line];  
	END CATCH
END

• As the exception is encountered, the control is transferred to the BEGIN CATCH block.
• You can print the information about the exception with the help of some built-in functions.
• These functions store the information about the exception that is encountered.
• ERROR_NUMBER() returns the error number which is assigned to that particular error in the table of errors.
• ERROR_SEVERITY() returns the severity of the exception. It stored an integer value depicting the severity. A higher value means that the severity of the error is high.
• ERROR_STATE() returns the state number of the error. It returns a null value we call it outside the CATCH block.
• ERROR_PROCEDURE() returns the name of the stored procedure in which the error occurred.
• ERROR_MESSAGE() returns the message to be displayed to the user.
• ERROR_LINE() returns the line number of the procedure at which the error occurred.
• Execute the procedure to see the output.

USE [master]
GO
EXEC [dbo].PrintSum 5,7

Thus, you might have learned about TRY CATCH and how we can use some built-in functions to generate a customized output for an exception.

Throw custom exception in SQL Server stored procedure

In SQL Server, you can also throw custom exceptions. Throwing a custom exception means you can cause an exception to encounter an error at any part of your code according to your logic.

We use the THROW statement or the RAISERROR function for throwing a custom exception. Both functions do almost the same work. However, there are some differences between the two.

• The RAISERROR was introduced in SQL Server 7.0 and the THROW statement was introduced in SQL Server 2012. Microsoft suggests using the THROW statement as it is easier to use and has more functionalities than the RAISERROR function.

• The RAISERROR function keeps the record of only the last exception thrown. However, it is not the case with the THROW statement. The THROW statement keeps a record of all the recent exceptions.

• You can also use the THROW function without using any parameters specified. But, you cannot do this with the RAISERROR function.
• The THROW statement threw an error. It takes three parameters:
  • Error number, which should be always greater than or equal to 50000 and less than the INT range.
  • Error message, which you want to show to the user. It should be enough informative to the user.
  • Error state, the current state of the error. It can have values between 0 to 255 as it supports the TINYINT data type.
• You can also use the THROW() function without any parameter to throw an exception and transfer the control to the CATCH block, but there must be a CATCH block to handle the exception.

Throw custom exception in SQL Server stored procedure example

Now we will use the THROW statement in an example to see how we can generate custom exceptions in our stored procedure according to our logic.

• We will create a stored procedure to check whether the product is available in the Products table or not.

USE [master]
GO
CREATE PROCEDURE FindProduct @ProductID int
AS
BEGIN
	IF EXISTS(SELECT * FROM Products where [Product ID]=@ProductID)
		PRINT('Product is Available in the Inventory')
	ELSE
		THROW 50001,'Error! No prodcuct with this ID',1
	PRINT('Thank You')
END

• If you execute the procedure with a valid Product ID, it will display the message as ‘Product is Available in the Inventory’ and then ‘Thank You’ in the next line.

USE [master]
GO
EXEC FindProduct 1333

• But, when you execute the procedure with any invalid Product ID, it will throw an error with a custom error number, a custom message, and a custom state that you defined in the THROW statement.

USE [master]
GO
EXEC FindProduct 1333

• Note that the statements after the THROW will not execute.

Hence, you might have learned how we can use the throw statement in a stored procedure in SQL Server to throw a custom exception.

Read: SQL Server Replace Function + Example

Exception handling in SQL server function

In this section, we will learn how we can use exception handling or error handling with a function. SQL Server does not support writing exception handling blocks inside a function.

But there is an alternative. You can execute the function which may cause an error, inside a TRY CATCH block and handle the exception. Let us understand this concept with an example.

• Let us take the example of a number divided by zero.
• We will create a function to divide two numbers.

USE [master]
GO
CREATE FUNCTION [dbo].ThrowExcept(@Number1 int, @Number2 int)
RETURNS real
AS
BEGIN
	DECLARE
		@Result real
	SET @Result=@Number1/@Number2
	RETURN @Result
END

• When we want to execute the function, we will execute it inside a TRY-CATCH block.

USE [master]
GO
DECLARE
	@result real
BEGIN TRY
	EXEC @result= [dbo].ThrowExcept 10, 2
	PRINT('Result is:'+STR(@result))
END TRY
BEGIN CATCH
	PRINT('You cannot divide a number by zero.
	Please change the second number')
END CATCH

• This time, we will give normal input and we will get the expected result.

• Now execute the function again and this time give 0 as the second number.
• The SQL Server will try to divide the number by zero and will throw an exception and transfer the control to the CATCH block.
• The CATCH block will have the code to handle the exception.

USE [master]
GO
DECLARE
	@result real
BEGIN TRY
	EXEC @result= [dbo].ThrowExcept 10, 0
	PRINT('Result is:'+STR(@result))
END TRY
BEGIN CATCH
	PRINT('You cannot divide a number by zero.
	Please change the second number')
END CATCH

Thus, we cannot use the TRY CATCH block inside a function, but we can execute the function inside a TRY CATCH block to handle the exception encountered during the function’s execution.

SQL Server stored procedure error handling best practices

In this section, you will learn how you should use exception handling in the best way. We will share some tips regarding the use of exception handling in SQL server 2019.

• Always use the TRY CATCH block where you think there is a logical group of statements. In these situations, there are higher chances of errors.
• Try to hide the technical details from the user. The user may not understand the technical details sometimes. These can also confuse the user.
• Print user-friendly messages. The message that you print should be clear and easy to understand for the user.
• Use generic code for error handling. Do not use too much complex code for exception handling, such as nesting at multiple levels.
• Create log tables to store the information about the errors encountered at the time of execution. This sometimes helps in a situation when you want to refer to any previous error.
• Use stored procedures for storing logs.
• Create some particularly stored procedures to handle the errors. Call the suitable stored procedure in the CATCH block to handle a particular type of exception.

You may like the following sql server tutorials:

• SQL Server Convert Datetime to String
• How to execute function in SQL with parameters
• SQL Server Agent won’t start

Thus, that was all about this article. Hope you have learned enough about error handling or exception handling in SQL Server. You can use it in your own ways for practice.

• What is Exception handling in SQL Server?
• Types of exceptions in SQL Server
• Try Catch in SQL Server
• Exception handling in SQL Server stored procedure
• Try catch in SQL Server stored procedure example
• Throw custom exception in SQL Server stored procedure
• Throw custom exception in SQL Server stored procedure example
• Exception handling in SQL server function
• SQL Server stored procedure error handling best practices