Using Try-Catch for Error Handling

— By Lori Brown @SQLSupahStah

While Try-Catch has been around since SQL 2005, you would be surprised at how infrequently applications and SQL programmers use this to handle errors. Since I also write my own maintenance and monitoring code and jobs that has to be deployed to my clients, I started making an effort to handle errors especially in implementation scripts and in the jobs that I create.

Here is a simple example of a Try-Catch block:

BEGIN TRY

[Some SQL Statement]

END TRY

BEGIN CATCH

[Error Handling or some other SQL Statement]

END CATCH

Basically the Try block is placed around the statement that you want to capture error information on and the Catch block is placed around the statement that defines what you want to happen if there is an error. Pretty simple!  🙂

There are some rules….A Try block must be immediately followed by a Catch block and only catches errors that have severity 10 or higher so it will catch a lot of things. You can use them in a ton of places (triggers, jobs, stored procedures to name a few) and you can nest Try-Catch blocks but make sure you know where the block will exit to if it encounters an error. It won’t catch errors with severity 20 or above, compile errors, or anything that breaks or disconnects a connection (like a KILL statement).

There are handy system functions that can be used to get information on the error while in the Catch block.

• ERROR_NUMBER() returns the number of the error.
• ERROR_SEVERITY() returns the severity.
• ERROR_STATE() returns the error state number.
• ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
• 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.

I use a Try-Catch block in a job that is used to create a report. Since the report generation is done using a CLR, when it fails it can give some uninformative info as to why which always makes it difficult to troubleshoot. I also wanted to capture run information for my reporting jobs and created a table designed to hold that info along with error messages captured by the Catch blocks should they occur.

— create ReportExecInfo table

CREATE TABLE ReportExecInfo(

ReportName VARCHAR(100) NOT NULL,

Last_Run DATETIME NULL,

Error VARCHAR(1) NULL,

ErrorMessage VARCHAR(100) NULL

) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX [PK_ReportExecInfo] ON [dbo].[ReportExecInfo]

(

ReportName ASC

)WITH (FILLFACTOR = 90) ON [PRIMARY]

GO

— Insert report names

INSERT INTO ReportExecInfo (ReportName) VALUES (‘DeadlockReport’)

INSERT INTO ReportExecInfo (ReportName) VALUES (‘BlockingReport’)

INSERT INTO ReportExecInfo (ReportName) VALUES (‘WeeklyHealthReport’)

GO

Then the below code is placed in a job that will log success and failure of the report to generate. I am using the ERROR_MESSAGE() function to grab the error message if one is generated.

DECLARE @rptname VARCHAR(200)

DECLARE @rptpath VARCHAR(200)

DECLARE @attchpath VARCHAR(500)

DECLARE @dtstmp VARCHAR(50)

DECLARE @ServerName VARCHAR(20)

DECLARE @NumRows INT

DECLARE @NumDays INT

DECLARE @cmd VARCHAR(500)

SET @NumDays = 7

SET @rptpath = ‘M:\Reports’

SET @ServerName = @@SERVERNAME

SET @dtstmp = CAST(DATEPART(yyyy, GETDATE())AS VARCHAR(4))+CAST(DATEPART(mm, GETDATE())AS VARCHAR(2))+CAST(DATEPART(dd, GETDATE())AS VARCHAR(2))

SET @rptname = @ServerName+‘-WeeklyHealthReport-‘+@dtstmp

SET @attchpath = @rptpath+‘\’+@rptname+‘.htm’

SET @cmd = ‘EXEC ReportController @Days=’+CAST(@NumDays AS VARCHAR(2))

BEGIN TRY

 — Use TRY to catch error if CTE fails

EXEC ADMIN.dbo.RetrieveSQLQueryAndProduceHTMLOutputFile_s @rptpath, @rptname, @cmd, @NumRows

END TRY

BEGIN CATCH

— Capture reason why CTE failed to produce report

UPDATE ReportExecInfo SET Last_Run = GETDATE(), Error = ‘Y’, ErrorMessage = LEFT(ERROR_MESSAGE(), 100) WHERE ReportName = ‘WeeklyHealthReport’

END CATCH

EXEC msdb.dbo.sp_send_dbmail

@profile_name = ‘DBMail’,

@recipients = ‘DBA@company.com’,

@subject =‘Weekly Health Report’,

@file_attachments = @attchpath;

— Update ReportExecInfo table with successful run date

UPDATE ReportExecInfo SET Last_Run = GETDATE(), Error = ‘N’ WHERE ReportName = ‘WeeklyHealthReport’

I ran my reports and forced a failure of the CLR on one. Here is what my report log table looks like:

What I am doing is pretty simple but I am sure that you can think of additional ways to use this type of functionality. More info on TRY-CATCH can be found at https://msdn.microsoft.com/en-us/library/ms175976.aspx

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRX@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!