TRY_CATCH

TRY- CATCH

 

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

    --  divide-by-zero error.

    SELECT 1/0

END TRY

BEGIN CATCH

    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;

END CATCH;

output

ErrorNumber	ErrorSeverity	ErrorState	ErrorProcedure	ErrorLine	ErrorMessage
8134	16	1	NULL	3	Divide by zero error encountered.

 

 

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 nvarchar(100),

            ErrorLine int,

            ErrorMessage nvarchar(1000))

GO

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

 

CREATE PROCEDURE prInsertError

AS

INSERT LogErrors

SELECT GETDATE(),

    ERROR_NUMBER(),

    ERROR_SEVERITY(),

    ERROR_STATE(),

    ERROR_PROCEDURE(),

    ERROR_LINE(),

    ERROR_MESSAGE() ;

GO

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

    SELECT 1/0

END TRY

BEGIN CATCH

    EXEC prInsertError

END CATCH;

 

 

BEGIN TRY

    SELECT convert(int,'a')

END TRY

BEGIN CATCH

    EXEC prInsertError

END CATCH;

 

BEGIN TRY

    SELECT convert(tinyint,300)

END TRY

BEGIN CATCH

    EXEC prInsertError

END CATCH;

--// Error Output..

SELECT * FROM LogErrors

 

ErrorTime	ErrorNumber	ErrorSeverity	ErrorState	ErrorProc	ErrorLine	ErrorMessage
14-Mar-12	8134	16	1	NULL	2	Divide by zero error encountered.
14-Mar-12	245	16	1	NULL	10	Conversion failed when converting the varchar value 'a' to data type int.
14-Mar-12	220	16	2	NULL	18	Arithmetic overflow error for data type tinyint, value = 300.