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.
    CREATE TABLE [dbo].[Error_StoreProcedure]( 
        [ID] [bigint] IDENTITY(1,1) NOT NULL, 
        [ErrorNumber] [varchar](50) NULL, 
        [ErrorSeverity] [varchar](50) NULL, 
        [ErrorState] [varchar](50) NULL, 
        [ErrorProcedure] [varchar](500) NULL, 
        [ErrorLine] [varchar](50) NULL, 
        [ErrorMessage] [varchar](max) NULL, 
        [EntryDate] [datetime] NULL, 
     CONSTRAINT [PK_Error_StoreProcedure] PRIMARY KEY CLUSTERED  
    ( 
        [ID] ASC 
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
    GO

After creating the above table we need to create one procedure for saving the error in the table;  see below.
 CREATE PROCEDURE usp_GetErrorInfo   
    AS   
    BEGIN 
        INSERT INTO Error_StoreProcedure SELECT   
        ERROR_NUMBER() AS ErrorNumber   
        ,ERROR_SEVERITY() AS ErrorSeverity   
        ,ERROR_STATE() AS ErrorState   
        ,ERROR_PROCEDURE() AS ErrorProcedure   
        ,ERROR_LINE() AS ErrorLine   
        ,ERROR_MESSAGE() AS ErrorMessage 
        ,dbo.GetDateTimeZone()   
    END


After creating the above procedure now we have to use the above procedure inside the other procedure.
    CREATE PROCEDURE TESTING_ERROR_PROCEDURE 
      
    AS 
    BEGIN 
     SET NOCOUNT ON; 
     
        BEGIN TRY   
             
            -- Generate divide-by-zero error.   
            SELECT 1/0;   
         
        END TRY   
        BEGIN CATCH   
             
            -- Execute error retrieval routine.   
            EXECUTE usp_GetErrorInfo;   
         
        END CATCH;    
     
    END 
    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.

European SQL 2016 Hosting
European best, cheap and reliable ASP.NET hosting with instant activation. HostForLIFE.eu is #1 Recommended Windows and ASP.NET hosting in European Continent. With 99.99% Uptime Guaranteed of Relibility, Stability and Performace. HostForLIFE.eu security team is constantly monitoring the entire network for unusual behaviour. We deliver hosting solution including Shared hosting, Cloud hosting, Reseller hosting, Dedicated Servers, and IT as Service for companies of all size.