Exception Handling in SQL Server

Introduction

In this article, you will learn Exception Handling using TRY-CATCH statements in SQL Server.

What is Exception Handling?

When a program executes an error, it is known as an exception, and the process of resolving an exception is called exception handling. All T-SQL statements can be placed in a TRY Block, and the code for exception handling can be placed in a CATCH Block.

Now let's see what to TRY-CATCH syntax looks like. There are two blocks in this syntax.

BEGIN TRY 
   -- SQL statements that may cause exceptions
END TRY
BEGIN CATCH 
   -- SQL statements that handle exception
END CATCH 

In exception handling, all T-SQL statements are placed in the try block. In the try block, if all statements are executed with no error, then we are good. Otherwise, control will go to the catch block. Inside the CATCH statement, we can try to correct the error, report it, or even log it so that we know when the error occurred and who logged it. Below are functions we can use inside the CATCH block to have detailed information and who logged it. Below are functions we can use inside the CATCH block to have detailed information.

• ERROR_NUMBER: Return the internal error number, and it has the same value as the @@ERROR function.
• ERROR_LINE: Return the line number on which an error occurred.
• ERROR_SEVERITY: This returns the error severity level.
• ERROR_STATE: This will return the info about the resource.
• ERROR_PROCEDURE: This will give you the name of the stored proc, function, or trigger that caused the error.
• ERROR_MESSAGE: This function returns the full text of the error message generated.

Now let’s understand Exception Handling with some examples.

Create stored procedures that handle errors.

CREATE OR ALTER PROC usp_DivideByZeroExceptionHandling
@Number1 INT,
@Number2 INT,
@Result INT OUTPUT
AS
BEGIN
	BEGIN TRY
		SET @Result = @Number1/@Number2
	END TRY
	BEGIN CATCH
		SELECT
			ERROR_MESSAGE() AS ErrorMessage,
			ERROR_LINE() AS ErrorLine,
			ERROR_NUMBER() AS ErrorNumber,
			ERROR_PROCEDURE() AS ErrorProc
	END CATCH 
END

Stored procedure usp_DivideByZeroExceptionHandling was created, as you can see in the above screenshot. We will go with a couple of scenarios to see how it handles errors.

Scenario 1. Execute the above procedure by passing parameter values that will not satisfy the condition.

DECLARE @R INT 
EXECUTE usp_DivideByZeroExceptionHandling 50, 0, @R OUTPUT
SELECT @R AS DivisonResults

After executing the procedure, we can see the code moved to the CTACH block as it didn’t satisfy the conditions, and as a result, it is giving ErrorMessage, ErrorLine, ErrorNumber, and ErrorProc.

Scenario 2. Execute the above procedure by passing parameter value which will satisfy the condition.

DECLARE @R INT 
EXECUTE usp_DivideByZeroExceptionHandling 50, 10, @R OUTPUT
SELECT @R AS DivisonResults

After executing the procedure, we can see the code runs on TRY BLOCK without error as it satisfies the conditions, and we got the DivisonResults as 5.

Handling Error with @@ERROR

Before the try-catch block was introduced in SQL Server, @@ERROR was used to report errors. Unlike try..catch, @ERROR doesn’t catch the errors but gives the error number for the previous T-SQL statement executed. The constraint with @ERROR is that if there is another statement executing after the error occurred, @@ERROR will reset its value to NULL, and hence, we need to check the value of @@ERROR after each anticipated SQL statement.

Conclusion

In this article, we have learned how to handle exceptions using try..catch block. We took two examples to demonstrate the feature. Hope you’ve liked the article. Please share your comments/feedback in the comment below.