Click here to Skip to main content
15,844,607 members
Articles / Database Development / SQL Server
Tip/Trick

Try Catch in SQL - Reporting stored procedure error in email and also log the error in a table

Rate me:
Please Sign up or sign in to vote.
4.25/5 (3 votes)
23 Jul 2013CPOL2 min read 13.8K   197   12   1
Reporting stored procedure error in email and also log the error in a table.

Introduction

Idea: Report an error while executing a stored procedure in email and also log the error in the table.

How to know immediately when there is an error while executing a stored procedure when an application is running or SQL job is running. Just like try and catch statements in application code, we can use try and catch in Stored Procedures and report the error in email and also log it in the table. Four things that we would be interested to log are Stored Procedure Name, Database Name, Error Message and Line number in stored Procedure.

Background

Try and catch in SQL works the same way as it works in Application Code.

Using the code

Step 1: Download the attachment. It has two files.

uspReportStoredProcedureErrorInEmail.txt has a stored procedure.

MailHelperSQLUserDefinedFunctions.txt has few SQL user defined functions.

Step 2: Create the stored procedure, uspReportStoredProcedureErrorInEmail, in the file, uspReportStoredProcedureErrorInEmail.txt in your database. This stored procedure has the code that will report the error in email and also log the error in a table.

In this stored Procedure, please update the profile name in the line(86) mentioned below.

SQL
EXEC MSDB.DBO.SP_SEND_DBMAIL @PROFILE_NAME='DBMAIL'

Step 3: Create the functions in the file MailHelperSQLUserDefinedFunctions.txt in msdb database. These functions are used to format the mail which is sent from stored procedure uspReportStoredProcedureErrorInEmail. The stored procedure expects the function to be in msdb database. Idea behind creating the functions in msdb is to use the functions across all the databases in the server.

Step 4: Create a sample stored procedure to know how it works. Sample stored procedure is given below. Please create it in your database. Stored procedure having a line which tries to divide 1 by 0.

SQL
Create proc [dbo].[ps_drop] 
as
Begin try
print 1/0
End try
Begin catch
exec uspReportStoredProcedureErrorInEmail
End catch

Step 5: Now execute the Stored Procedure.

SQL
Exec [ps_drop]

Step 6: You must see the Message as shown below:

Mail goes to: XYZ@gmail.com
CC in Mail to : XYZ@gmail.com
[DATABASENAME]
Mail queued.

Step 6: So why is the mail sent to XYZ@gmail.com and which table did it log the error?

Executing this stored procedure for the first time creates two tables.

  1. ErrorReceivers
  2. ErrorLogOfStoredProcedures

ErrorReceivers has the mail ids. Mail is sent to two mails with id 1 and 2 in the table.

Errors are logged in the table ErrorLogOfStoredProcedures.

Step 7: Update the mailids in ErrorReceivers Table and execute the stored procedure. You should get the email to the email your updated in the table and also check the log in table.

SQL
Exec [ps_drop]
Select * from ErrorLogOfStoredProcedures

Points of Interest

You can update the code to send mails to more than 2 people. Customize the style of mail by updating the functions which provide styles to stored procedure.

History

  • Article created for the first time on 23 July 2013.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 4 Pin
Sibeesh KV24-Sep-14 19:50
professionalSibeesh KV24-Sep-14 19:50 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.