Function that will identify why your code fails. (USING TRY CATCH)

Function in TRY CATCH

Always try to put your code in the TRY block and you check for the errors in the CATCH block. SQL Server has a bunch of functions that will help you identify why your code failed, here is a list of the functions and what they return

ERROR_NUMBER()
returns the number of the error

ERROR_SEVERITY()
returns the severity of the error

ERROR_STATE()
returns the error state number

ERROR_PROCEDURE()
returns the name of the stored procedure or trigger where the error occurred, this will be NULL if you run an ad-hoc SQL statement

ERROR_LINE()
returns the line number inside the routine that caused the error

ERROR_MESSAGE()
returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times

Let's run an example that generates a divide by zero error, in the catch we are just doing a simple select that calls the functions mentioned before to see what they return

begin try

      select 1/0 as 'DivisionResult'

end try

begin catch

      select

            error_number() as 'ErrorNumber',

            error_severity() as 'ErrorSeverity',

            error_state() as 'ErrorState',

            error_procedure() as 'ErrorProcedure',

            error_message() as 'ErrorMessage'

end catch

DivisionResult

--------------

(0 row(s) affected)

ErrorNumber ErrorSeverity ErrorState  ErrorProcedure  ErrorMessage

----------- ------------- ----------- --------------- -------------------------------

8134        16            1           NULL            Divide by zero error encountered.

(1 row(s) affected)

As you can see we got all that information back, that was pretty nice. Let's take it to the next step

Create the following table to store all the error information in

create table LogErrors (ErrorTime datetime,

                  ErrorNumber int,

                  ErrorSeverity int,

                  ErrorState int,

                  ErrorProc varchar(max),

                  ErrorLine int,

                  ErrorMessage varchar(max))

Command(s) completed successfully.

Create this stored procedure that will insert into the table we just created

create procedure proc_InsertError

AS

begin

      insert LogErrors

      SELECT GETDATE(),ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_PROCEDURE(),ERROR_LINE(),ERROR_MESSAGE()

End

Command(s) completed successfully.

Run these 3 queries, they will generate 3 inserts into the LogErrors table

begin try

      SELECT 1/0

end try

begin catch

    EXEC proc_InsertError

end catch;

       

begin try

    SELECT convert(int,'a')

end try

begin catch

    EXEC proc_InsertError

end catch;

       

begin try

    SELECT convert(tinyint,300)

end try

begin catch

      EXEC proc_InsertError

end catch;

If you check now what is in the table, you will see 3 rows

select * from LogErrors

ErrorTime  ErrorNumber  ErrorSeverity ErrorState  ErrorProc   ErrorLine ErrorMessage

----------- ----------- ------------- ----------- ----------  --------  -----------

2012-01-09  8134        16            1           NULL         2        Divide by zero error encountered.

2012-01-09  245         16            1           NULL         9        Conversion failed when converting the varchar value 'a' to data type int.

2012-01-09  220         16            2           NULL         16       Arithmetic overflow error for data type tinyint, value = 300.

(3 row(s) affected)

Post Reference: Vikram Aristocratic Elfin Share