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
