Post
Ask Question

How To Use TRY CATCH In SQL Procedure

In this post, we will learn how to use TRY CATCH in SQL procedure and store an error with error text. Here is a simple example for generating the error and storing it in a SQL table. Let's start coding.

For saving the error in the table first we need to create the table in SQL Database. See below.

  1. CREATE TABLE [dbo].[Error_StoreProcedure](  
  2.     [ID] [bigint] IDENTITY(1,1) NOT NULL,  
  3.     [ErrorNumber] [varchar](50) NULL,  
  4.     [ErrorSeverity] [varchar](50) NULL,  
  5.     [ErrorState] [varchar](50) NULL,  
  6.     [ErrorProcedure] [varchar](500) NULL,  
  7.     [ErrorLine] [varchar](50) NULL,  
  8.     [ErrorMessage] [varchar](max) NULL,  
  9.     [EntryDate] [datetime] NULL,  
  10.  CONSTRAINT [PK_Error_StoreProcedure] PRIMARY KEY CLUSTERED   
  11. (  
  12.     [ID] ASC  
  13. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  14. ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
  15. GO 
After creating the above table we need to create one procedure for saving the error in the table;  see below.
  1. CREATE PROCEDURE usp_GetErrorInfo    
  2. AS    
  3. BEGIN  
  4.     INSERT INTO Error_StoreProcedure SELECT    
  5.     ERROR_NUMBER() AS ErrorNumber    
  6.     ,ERROR_SEVERITY() AS ErrorSeverity    
  7.     ,ERROR_STATE() AS ErrorState    
  8.     ,ERROR_PROCEDURE() AS ErrorProcedure    
  9.     ,ERROR_LINE() AS ErrorLine    
  10.     ,ERROR_MESSAGE() AS ErrorMessage  
  11.     ,dbo.GetDateTimeZone()    
  12. END 
 After creating the above procedure now we have to use the above procedure inside the other procedure.
  1. CREATE PROCEDURE TESTING_ERROR_PROCEDURE  
  2.    
  3. AS  
  4. BEGIN  
  5.  SET NOCOUNT ON;  
  6.   
  7.     BEGIN TRY    
  8.           
  9.         -- Generate divide-by-zero error.    
  10.         SELECT 1/0;    
  11.       
  12.     END TRY    
  13.     BEGIN CATCH    
  14.           
  15.         -- Execute error retrieval routine.    
  16.         EXECUTE usp_GetErrorInfo;    
  17.       
  18.     END CATCH;     
  19.   
  20. END  
  21. GO 

The above procedure generates the error and goes to the CATCH part and saves all information of the error into our error table.

Run this query SELECT * FROM Error_StoreProcedure

See the output of the above table. Output displays procedure name and line number of the error. 

SQL try catch output