Error Logging in SQL   Leave a comment

While developing application we logs many different types of activities in order to track what is happening in the system.

We logs many different types of errors occurring in the application due to some abnormal operations or behavior of the system based on the activities happening in the system. These errors sometimes we logs into the file system or sometimes logs into the database and give the permission to admin user to get the access of these logs and take the necessary action.

Here, we will see a simple example to track and log the error in SQL server.

-- Verify that the stored procedure does not already exist.
IF OBJECT_ID ( 'usp_GetErrorInfo', 'P' ) IS NOT NULL 
    DROP PROCEDURE usp_GetErrorInfo;

-- Create procedure to retrieve error information.
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_LINE() AS ErrorLine
    ,ERROR_MESSAGE() AS ErrorMessage;

    -- Generate divide-by-zero error.
    SELECT 1/0 As TestData;
    -- Execute error retrieval routine.
    EXECUTE usp_GetErrorInfo;

Here in above sample SQL, we have created a stored procedure which we can call when there is an exception while executing the SQL statements.

This procedure will return the details of the SQL exception. You can modify the stored procedure as per your need to log the error into the database. And later you can refer the same table for further analysis.

You can customize the stored procedure and pass the different parameters to make it more descriptive like, you can pass the actual stored procedure name which generating exception as parameter, or name of actual function in front end calling this stored procedure or pass the other information like input pass to actual stored procedure which generated the SQL exception and log the information for future reference to provide the resolution for that.


Posted October 7, 2014 by Izharuddin Shaikh in SQL

Tagged with

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: