Back to: SQL Server Tutorial For Beginners and Professionals

Exception Handling Using Try Catch in SQL Server

In this article, I will discuss Exception Handling Using Try Catch in SQL Server with examples. From SQL Server 2005 we are provided with a structure error handling mechanism with the help of TRY and CATCH blocks which should be used as follows

Syntax:

BEGIN TRY
        -- statements which will cause the error
  -- statements which are related with the error that should not be execute when the error occurred
END TRY
BEGIN CATCH
  -- statements which should be executed when the error occurs
END CATCH
-- Any Other Statements

Any set of SQL statements that can possibly throw an exception need to be wrapped between the BEGIN TRY and END TRY blocks. If there is an exception in the TRY block, the control immediately jumps to the CATCH block. If there is no exception, CATCH block will be skipped, and the statements, after the CATCH block are executed. 

We need to remember one thing that the Errors trapped by a CATCH block are not returned to the calling application. If you want to return the error information back to the calling application then you need to use the RAISERROR() function explicitly with the catch block.

Let us see an example for better understanding.

Example1: Create a procedure for dividing 2 variables values by using TRY CATCH implementation with user-defined error statements.

CREATE PROCEDURE spDivideTwoNumbers
@Number1 INT, 
@Number2 INT
AS
BEGIN
  DECLARE @Result INT
  SET @Result = 0
  BEGIN TRY
    SET @Result = @Number1 / @Number2
    PRINT 'RESULT IS : '+CAST(@Result AS VARCHAR)
  END TRY
  BEGIN CATCH
    PRINT 'SECOND NUMBER SHOULD NOT BE ZERO'
  END CATCH
END

Execution:

EXEC spDivideTwoNumbers 10, 2

OUTPUT: RESULT IS : 5

EXEC spDivideTwoNumbers 10, 0

OUTPUT: SECOND NUMBER SHOULD NOT BE ZERO

When we execute the above stored procedure with correct values, then the error will not occur in the program, so after executing all the statements in the try block the control directly jumps to the statements present after catch block without executing the catch block.

If any error occurs in the execution process i.e. in the try block, then in such case from the line where the error got occurred, the control directly jumps to the catch block. So rest of the statement in the try block will not execute whereas catch block will execute.

NOTE: In our above program when the error got occurred we are displaying an error message “SECOND NUMBER SHOULD NOT BE ZERO”. In place of that error message we can also display the original error message associated with that data by calling a function “Error_Message”. To test this rewriting the code inside the catch block as following

Print Error_Message()

Example2: Create a procedure for dividing two variables values by using try catch implementation with system defined error statements.

ALTER PROCEDURE spDivideTwoNumbers
@Number1 INT, 
@Number2 INT
AS
BEGIN
  DECLARE @Result INT
  SET @Result = 0
  BEGIN TRY
    SET @Result = @Number1 / @Number2
    PRINT 'RESULT IS : '+CAST(@Result AS VARCHAR)
  END TRY
  BEGIN CATCH
    PRINT ERROR_MESSAGE()
  END CATCH
END

Execution:

EXEC spDivideTwoNumbers 10, 0

OUTPUT: Divide by zero error encountered.

What is ERROR_MESSAGE()?

This method is used to display what type of error has occurred in the try block.

Let us see a real-time example of using try catch implementation in SQL Server.

Please use below SQL Script to create and populate the Product and ProductSales table with some test data.

-- Create Product table
CREATE TABLE Product
(
  ProductId INT PRIMARY KEY,
  Name VARCHAR(50),
  Price INT,
  QuantityAvailable INT
)
GO

-- Populate the Product Table with some test data
INSERT INTO Product VALUES(101, 'Laptop', 1234, 100)
INSERT INTO Product VALUES(102, 'Desktop', 3456, 50)
INSERT INTO Product VALUES(103, 'Tablet', 5678, 35)
INSERT INTO Product VALUES(104, 'Mobile', 7890, 25)
GO

-- Create ProductSales table
CREATE TABLE ProductSales
(
  ProductSalesId INT PRIMARY KEY,
  ProductId INT,
  QuantitySold INT
) 
GO

SELECT * FROM Product will give us the below output

Create the following stored procedure for product sales.

CREATE PROCEDURE spSellProduct
@ProductID INT,
@QuantityToSell INT
AS
BEGIN
  -- First we need to Check the stock available for the product we want to sell
  DECLARE @StockAvailable INT

  SELECT @StockAvailable = QuantityAvailable 
  FROM Product 
  WHERE ProductId = @ProductId

  -- We need to throw an error to the calling application 
  -- if the stock is less than the quantity we want to sell
  IF(@StockAvailable< @QuantityToSell)
  BEGIN
    Raiserror('Enough Stock is not available',16,1)
  END
  -- If enough stock is available
  ELSE
  BEGIN
    BEGIN TRY
      -- We need to start the transaction
      BEGIN TRANSACTION

      -- First we need to reduce the quantity available
      UPDATE	Product SET 
          QuantityAvailable = (QuantityAvailable - @QuantityToSell)
      WHERE	ProductID = @ProductID

      -- Calculate MAX ProductSalesId
      DECLARE @MaxProductSalesId INT
      SELECT	@MaxProductSalesId = CASE 
          WHEN  MAX(ProductSalesId) IS NULL THEN 0 
          ELSE MAX(ProductSalesId) 
          END 
      FROM	ProductSales

      -- Increment @MaxProductSalesId by 1, so we don't get a primary key violation
      Set @MaxProductSalesId = @MaxProductSalesId + 1

      -- We need to insert the quantity sold into the ProductSales table
      INSERT INTO ProductSales(ProductSalesId, ProductId, QuantitySold)
      VALUES(@MaxProductSalesId, @ProductId, @QuantityToSell)

      COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
      ROLLBACK TRANSACTION
      SELECT	ERROR_NUMBER() as ErrorNumber,
          ERROR_MESSAGE() as ErrorMessage,
          ERROR_PROCEDURE() as ErrorProcedure,
          ERROR_STATE() as ErrorState,
          ERROR_SEVERITY() as ErrorSeverity,
          ERROR_LINE() as ErrorLine
    END CATCH
  End
END

In procedure spSellProduct the Begin Transaction and Commit Transaction statements are wrapped between the Begin Try and End Try block. If there is no error occurred in the code that is enclosed within the TRY block, then the COMMIT TRANSACTION statement gets executed and the changes are made permanent.

On the other hand, if there is an error within the try block then the control immediately jumps to the CATCH block and in the CATCH block, we are rolling back the transaction. So it’s much easier to handle errors with Try/Catch construct than with @@Error system function.

SQL Server also provides some built-in function that we can use in the scope of a CATCH block which is used to retrieve more information about the error that occurred and these functions will return NULL if they are executed outside the scope of the CATCH block.

We cannot use the TRY/CATCH implementation within a user-defined function. 

In the next article, I will discuss Transaction Control Language in SQL Server with examples.

SUMMARY

In this article, I try to explain Exception Handling Using Try Catch in SQL Server step by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.