TRY...CATCH And RAISERROR With Stored Procedure

In this article, we’ll see how to handle the error and raise an error in SQL Server with a stored procedure.

Primary Discussion

TRY…CATCH is one of the great options for error handling in SQL Server for various programming languages, such as C#, Java, PHP etc. It’s a great relief for developers because it shows an actual exception and is considered the easiest way. For error handling in SQL Server, TRY…CATCH was introduced with SQL Server 2005. It’s similar to the one in C#, but in SQL Server, finally, the code block doesn’t exist.

In C#, to be on the safe side, we can operate any operation within the transaction open/close or rollback. But I think it would be good practice to use TRY…CATCH in SQL. In some cases, I have experienced that developers sometimes forget to use transaction. So it could be dangerous in some scenarios like financial transactions.

SYNTAX

BEGIN TRY
    BEGIN TRANSACTION
     /* Your sql_statement | statement_block*/
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION
    /* Your sql_statement | statement_block*/
     RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
END CATCH

Let’s work step-by-step with some real data and examples.

Step 1

Here, we’re going to create a database named CSharpCorner and two tables - Department and Employee  - with a One-to-Many relationship between Department and Employee. Also, we will insert some values into both the tables.

USE master
GO
CREATE DATABASE CSharpCorner
GO
USE CSharpCorner
GO
Create Table Department
(
Id INT IDENTITY(1,1) PRIMARY KEY,
DeptName VARCHAR(50) NOT NULL
)
Go

 INSERT INTO dbo.Department (DeptName)
  VALUES  ('HR'),('IT'),('MKT')

CREATE TABLE Employee
(
 Id INT IDENTITY(1,1) PRIMARY KEY,
 Name VARCHAR(50) NOT NULL,
 Age INT NOT NULL,
 Email VARCHAR(50),
 DeptId INT NOT NULL REFERENCES Department(Id)
 )
GO

 INSERT INTO Employee
         (Name, Age, Email,DeptId )
 VALUES  ('Tamim Iqbal',27,'[email protected]',1),
         ('Sakib Al-Hasan',27,'[email protected]',1),
         ('Mushfiqur Rahim ',20,'[email protected]',2)
GO

SELECT * FROM Employee
GO

Step 2

In this step, we’ll create a stored procedure which will update the Employee’s value and then, delete the data from the Department table. Let’s execute the following code.

-- Verify that the stored procedure does not already exist.
IF OBJECT_ID ( 'usp_UpdateEmpDeleteDeptId', 'P' ) IS NOT NULL
    DROP PROCEDURE usp_UpdateEmpDeleteDeptId;
GO

-- Create a procedure to retrieve the error information.
 CREATE PROCEDURE usp_UpdateEmpDeleteDeptId
 @Id INT,
 @EmpName VARCHAR(50),
 @Email VARCHAR(50)
 AS
 BEGIN TRY
  BEGIN TRANSACTION

    UPDATE Employee
    SET Name=@EmpName
    WHERE Id=@Id

    DELETE FROM Department
    WHERE Id=@Id

   COMMIT TRANSACTION
 END TRY
 BEGIN CATCH
    IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION
    DECLARE  @ErrorMessage  NVARCHAR(4000),
             @ErrorSeverity INT,
             @ErrorState    INT;

    SELECT
        @ErrorMessage  = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState    = ERROR_STATE();

    SET @ErrorMessage=@ErrorMessage

    RAISERROR (@ErrorMessage, -- Message text.
                 @ErrorSeverity, -- Severity.
                 @ErrorState -- State.
               );
 END CATCH
 GO

Stored Procedure definition starts and ends with BEGIN …END block respectively with TRY….CATCH. Here, we have added a BEGIN TRANSACTION option inside the TRY block and after the SQL statement, we call COMMIT TRANSACTION to finally save our execution.

The TRY block first goes for its code execution. If there is any error raised, then our COMMIT TRANSACTION will be skipped and then the CATCH block will be executed. Here, the ROLLBACK TRANSACTION option will execute and all the changes will undo; this means the table state will be the same as it was. All executions in the TRY block will be ignored. At the beginning of the CATCH block, I used an @@TRANCOUNT function to confirm if any transaction is still open. @@TRANCOUNT is an SQL built-in function which returns the number of transactions running in our current session. Then, the CATCH block will use the RAISERROR function to give a specific error.

 

RAISERROR

SQL Server has its own mechanism to give the exact information of the exception occurred. This mechanism is called RAISERROR. You can pass your own customized message dynamically or use a built-in message from sys.messages catalog view. In this blog, we have passed a variable which gets the data from different SQL built-in functions. Let’s have a little idea of these.

• ERROR_MESSAGE()
  This function returns a NVARCHAR(4000) type value. ERROR_MESSAGE() gives the complete text message of the exception/error occoured.
  
  
• ERROR_SEVERITY() 
  The ERROR_SEVERITY() function returns an INT type value which indicates a specific severity error occurred in that session.
  
  
• ERROR_STATE()
  In SQL Server, some error messages can be raised for more many reasons and the ERROR_STATE() function makes the error specific from others. It also returns an INT value.

Step 3

 

Try to execute the following code and let’s see what happens.

SELECT * FROM Employee
GO
EXECUTE usp_UpdateEmpDeleteDeptId 1,'Mashrafe Bin Mortaza','[email protected]'
GO
SELECT * FROM Employee
GO

Analysis

 

We tried to update the employee name and email where Id is 1 and we also tried to delete the Department where Department Id is 1. But, because of the Foreign Key/Referential Integrity, we can’t delete the Department, that’s why an error occurred and our COMMIT TRANSACTION was skipped. The CATCH block executed the ROLLBACK TRANSACTION and that’s why the update operation skipped. 

Step 4

Once again, execute the previous code just by changing the first value of the SP 1 to 3.

SELECT * FROM Employee
GO
EXECUTE usp_UpdateEmpDeleteDeptId 3,'Mashrafe Bin Mortaza','[email protected]'
GO
SELECT * FROM Employee
GO

Analysis

Here, the Department Id 3 has no reference data in Employee table so the referential integrity will not work. So, both our operations - Update and Delete - succeeded.

Conclusion

As you can see, SQL Server makes it easy to handle the errors by using TRY….CATCH for you. The TRY….CATCH blocks and RAISERROR () can function together to make the developers' lives easier and painless.