Mahmoud ElMansy: knowledge meant to be free

SQL Server Error Handling and Log Stored Procedures Errors

In my Daily work i face lots of unexpected sql errors .
And because i use .net it’s not possible to trace or debug stored procedures .
We all know that SQL support try catch clause .
In this post we will use try catch clause to log Stored Procedures errors in Log table called “StoredErrorLogs”.

This is simple script shows how we can do that.

CREATE PROCEDURE dbo.[Proceduare_Name]
AS
BEGIN TRY

--your SQL code

END TRY
BEGIN CATCH
insert into StoredErrorLogs select
ERROR_NUMBER(),
ERROR_MESSAGE(),
ERROR_SEVERITY() ,
ERROR_STATE() ,
ERROR_LINE() ,
ERROR_PROCEDURE()

END CATCH
GO

 

 

This will be very helpful to trace and log all your stored errors.