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

• Download source code - 35.4 KB

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.

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.

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.

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.

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.