Post
Ask Question

TRY...CATCH And RAISERROR With 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
  1. BEGIN TRY    
  2.     BEGIN TRANSACTION  
  3.      /* Your sql_statement | statement_block*/  
  4.     COMMIT TRANSACTION  
  5. END TRY    
  6. BEGIN CATCH   
  7.     IF @@TRANCOUNT > 0  
  8.     ROLLBACK TRANSACTION  
  9.     /* Your sql_statement | statement_block*/   
  10.      RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)  
  11. 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.
  1. USE master  
  2. GO  
  3. CREATE DATABASE CSharpCorner  
  4. GO  
  5. USE CSharpCorner  
  6. GO  
  7. Create Table Department  
  8. (  
  9. Id INT IDENTITY(1,1) PRIMARY KEY,  
  10. DeptName VARCHAR(50) NOT NULL  
  11. )  
  12. Go  
  13.   
  14.  INSERT INTO dbo.Department (DeptName)  
  15.   VALUES  ('HR'),('IT'),('MKT')  
  16.   
  17. CREATE TABLE Employee  
  18. (  
  19.  Id INT IDENTITY(1,1) PRIMARY KEY,  
  20.  Name VARCHAR(50) NOT NULL,  
  21.  Age INT NOT NULL,  
  22.  Email VARCHAR(50),  
  23.  DeptId INT NOT NULL REFERENCES Department(Id)  
  24.  )  
  25. GO   
  26.   
  27.  INSERT INTO Employee  
  28.          (Name, Age, Email,DeptId )  
  29.  VALUES  ('Tamim Iqbal',27,'[email protected]',1),  
  30.          ('Sakib Al-Hasan',27,'[email protected]',1),  
  31.          ('Mushfiqur Rahim ',20,'[email protected]',2)  
  32. GO  
  33.   
  34. SELECT * FROM Employee  
  35. 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.
  1. -- Verify that the stored procedure does not already exist.    
  2. IF OBJECT_ID ( 'usp_UpdateEmpDeleteDeptId', 'P' ) IS NOT NULL     
  3.     DROP PROCEDURE usp_UpdateEmpDeleteDeptId;    
  4. GO   
  5.   
  6. -- Create a procedure to retrieve the error information.    
  7.  CREATE PROCEDURE usp_UpdateEmpDeleteDeptId   
  8.  @Id INT,  
  9.  @EmpName VARCHAR(50),  
  10.  @Email VARCHAR(50)  
  11.  AS    
  12.  BEGIN TRY  
  13.   BEGIN TRANSACTION  
  14.       
  15.     UPDATE Employee   
  16.     SET Name=@EmpName  
  17.     WHERE Id=@Id  
  18.   
  19.     DELETE FROM Department   
  20.     WHERE Id=@Id  
  21.         
  22.    COMMIT TRANSACTION  
  23.  END TRY  
  24.  BEGIN CATCH  
  25.     IF @@TRANCOUNT > 0  
  26.     ROLLBACK TRANSACTION  
  27.     DECLARE  @ErrorMessage  NVARCHAR(4000),  
  28.              @ErrorSeverity INT,    
  29.              @ErrorState    INT;    
  30.   
  31.     SELECT     
  32.         @ErrorMessage  = ERROR_MESSAGE(),    
  33.         @ErrorSeverity = ERROR_SEVERITY(),   
  34.         @ErrorState    = ERROR_STATE();   
  35.            
  36.     SET @ErrorMessage=@ErrorMessage   
  37.   
  38.     RAISERROR (@ErrorMessage, -- Message text.    
  39.                  @ErrorSeverity, -- Severity.    
  40.                  @ErrorState -- State.    
  41.                );    
  42.  END CATCH  
  43.  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.
  1. SELECT * FROM Employee  
  2. GO  
  3. EXECUTE usp_UpdateEmpDeleteDeptId 1,'Mashrafe Bin Mortaza','[email protected]'  
  4. GO  
  5. SELECT * FROM Employee  
  6. 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.
  1. SELECT * FROM Employee  
  2. GO  
  3. EXECUTE usp_UpdateEmpDeleteDeptId 3,'Mashrafe Bin Mortaza','[email protected]'  
  4. GO  
  5. SELECT * FROM Employee  
  6. 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.
Next Recommended Reading How To Use TRY CATCH In SQL Procedure