Using a TRY CAT…

Using a TRY CATCH BLOCK IN SQL CODE FOR trapping Errors

 

When a stored procedure is executed in unattended mode, it could throw one or many error or exceptions.  There is more than one way to try and catch this errors and send it as a mail to the users when stored procedure fails to execute.  Today I will discuss one method to display this message using two important SQL Functions/Tasks.

Step 1: Catch the exception in a container and store in SQL table

Step 2: Mail the information in the container to the users

Step1 : Catch the exception: For this we often use the TRY CATCH BLOCK WITHIN THE SQL Stored Procedure.

Example

 

CREATE/ALTER PROCEDURE   <SCHEMA NAME>.< NAME OF PROCEDURE>

AS

SET NOCOUNT ON

BEGIN  TRY

<Logic for the Stored Proc is inserted here>

END TRY

BEGIN CATCH   

  SELECT  

ERROR_NUMBER()

                                , ERROR_SEVERITY()

                                , ERROR_STATE()

                                , ERROR_PROCEDURE()

                                , ERROR_LINE()

                                , ERROR_MESSAGE()

To do this we need to create a SQL Table as under:

CREATE TABLE ErrorLog

{

@error_number INT,

@error_severity INT,

@erro_state INT,

@procedure_name VARCHAR(MAX),

@error_line INT,

@error_message VARCHAR(MAX)

}

INSERT INTO ERRORLOG

VALUES

SELECT @error_number = ERROR_NUMBER()

                                , @error_severity = ERROR_SEVERITY()

                                , @erro_state = ERROR_STATE()

                                , @procedure_name = ERROR_PROCEDURE()

                                , @error_line = ERROR_LINE()

                                , @error_message = ERROR_MESSAGE()

 

–SELECT @emess AS ErrorMessage, @eline AS ErrorLine

   EXECUTE [usp_ErrorLogInsert]

 @ERROR_NUMBER  ,

@ERROR_SEVERITY ,

@ERROR_STATE ,

@ERROR_PROCEDURE ,

@ERROR_LINE ,

@ERROR_MESSAGE

END CATCH;

 If we want to catch the error messages into SQL server table, we need the following steps

Step 1: Create a Error Log Table on SQL Server.  We need appropriate table create permissions to

create a permanent table on SQL Server.

–Create Table

CREATE TABLE ErrorLog

{

Error_number INT,

Error_severity INT,

ErrorSstate INT,

ProcedureName VARCHAR(MAX),

ErrorLine INT,

@error_message VARCHAR(MAX),

 

}

 Step 2 : Create a procedure to insert data by called procedure.  The called procedure will use this procedure to send data and populate table

CREATE Procedure [dbo].[usp_ErrorLogInsert]

 (   @ERROR_NUMBER int

    ,@ERROR_SEVERITY int

    ,@ERROR_STATE int

    ,@ERROR_PROCEDURE AS varchar(max)

    ,@ERROR_LINE AS int

    ,@ERROR_MESSAGE varchar(max)

      )    

AS

 Set Nocount on

 INSERT INTO [dbo].[ErrorLog]

           ([ERROR_NUMBER]

           ,[ERROR_SEVERITY]

           ,[ERROR_STATE]

           ,[ERROR_PROCEDURE]

           ,[ERROR_LINE]

           ,[ERROR_MESSAGE])

     VALUES

           ( @ERROR_NUMBER

    ,@ERROR_SEVERITY

    ,@ERROR_STATE

    ,@ERROR_PROCEDURE

    ,@ERROR_LINE

    ,@ERROR_MESSAGE)

Store Stored procedure errors into a SQL table

Using a TRY CATCH BLOCK IN SQL CODE FOR trapping Errors

 

When a stored procedure is executed in unattended mode, it could throw one or many error or exceptions.  There is more than one way to try and catch this errors and send it as a mail to the users when stored procedure fails to execute.  Today I will discuss one method to display this message using two important SQL Functions/Tasks.

Step 1: Catch the exception in a container and store in SQL table

Step 2: Mail the information in the container to the users

 

Step1 : Catch the exception: For this we often use the TRY CATCH BLOCK WITHIN THE SQL Stored Procedure.

Example

 

CREATE/ALTER PROCEDURE   <SCHEMA NAME>.< NAME OF PROCEDURE>

AS

SET NOCOUNT ON

BEGIN  TRY

<Logic for the Stored Proc is inserted here>

END TRY

BEGIN CATCH   

  SELECT  

ERROR_NUMBER()

                                , ERROR_SEVERITY()

                                , ERROR_STATE()

                                , ERROR_PROCEDURE()

                                , ERROR_LINE()

                                , ERROR_MESSAGE()

 

When a SQL server fails to execute a query or a procedure, it displays the above system messages.  Using the TRY CATCH block we are collecting all of them in a place and convey it to the user.  If the SQL procedure is running unattended and you might like to create a SQL table and put the data in a table for logging purposes.

To do this we need to create a SQL Table as under:

CREATE TABLE ErrorLog

{

@error_number INT,

@error_severity INT,

@erro_state INT,

@procedure_name VARCHAR(MAX),

@error_line INT,

@error_message VARCHAR(MAX)

}

INSERT INTO ERRORLOG

VALUES

SELECT @error_number = ERROR_NUMBER()

                                , @error_severity = ERROR_SEVERITY()

                                , @erro_state = ERROR_STATE()

                                , @procedure_name = ERROR_PROCEDURE()

                                , @error_line = ERROR_LINE()

                                , @error_message = ERROR_MESSAGE()

 

–SELECT @emess AS ErrorMessage, @eline AS ErrorLine

   EXECUTE [usp_ErrorLogInsert]

 @ERROR_NUMBER  ,

@ERROR_SEVERITY ,

@ERROR_STATE ,

@ERROR_PROCEDURE ,

@ERROR_LINE ,

@ERROR_MESSAGE

END CATCH;

 

 

 

CREATE Procedure [dbo].[usp_ErrorLogInsert]

 (   @ERROR_NUMBER int

    ,@ERROR_SEVERITY int

    ,@ERROR_STATE int

    ,@ERROR_PROCEDURE AS varchar(max)

    ,@ERROR_LINE AS int

    ,@ERROR_MESSAGE varchar(max)

      )    

AS

 

Set Nocount on

 

 

INSERT INTO [dbo].[ErrorLog]

           ([ERROR_NUMBER]

           ,[ERROR_SEVERITY]

           ,[ERROR_STATE]

           ,[ERROR_PROCEDURE]

           ,[ERROR_LINE]

           ,[ERROR_MESSAGE])

     VALUES

           ( @ERROR_NUMBER

    ,@ERROR_SEVERITY

    ,@ERROR_STATE

    ,@ERROR_PROCEDURE

    ,@ERROR_LINE

    ,@ERROR_MESSAGE)