Have you written a TRY..CATCH statement in T-SQL? I hadn’t done it for most of my career, since the construct hadn’t existed. As a result, my code over the years is littered with catching @@error in a variable and then acting on that result.
However I’m trying to do better, and when I went to write one recently, I realized that I wasn’t doing it enough as I needed to check some syntax. Here’s a short post to try and capture that information and burn it into my brain.
The Syntax
The basic syntax is this:
BEGIN TRY
— do some work here.
END TRY
BEGIN CATCH
— error handling code here.
END CATCH
This almost seems funny as I’d expect a TRY with a BEGIN END block in the SQL language, but this reads better, and I think this is (Syntactically) a better implementation in the language.
Using TRY . . CATCH
The use of this is to do some work in the TRY block (BEGIN TRY..END TRY) and expect it to work. For example, I recently had this:
BEGIN TRY
SELECT TOP 10
cs.CustomerID
, cs.LastSale
, cs.Salesman
, CAST(cs.SaleValue AS NUMERIC)
FROM
dbo.CustomerSales AS cs;
END TRY
BEGIN CATCH
— CATCH BLOCK
END CATCH
SELECT @@rowcount
The TRY block is the place where I perform some work. If it works as expected, then I just continue on. In this case, this should be a simple query that runs, and when it finishes, the SELECT for the rowcount executes.
However, if some error occurs, execution immediately goes to the CATCH block. In that case, whatever I have in that space will execute and then the execution will continue.
Example
Let’s look at an example of how this works. Here’s my full TRY..CATCH with a few print statements to track the activity.
ALTER PROCEDURE spGetCommission
@userid INT
AS
PRINT ‘Before TRY’;
BEGIN TRY
PRINT ‘Start TRY’;
SELECT TOP 10
cs.CustomerID
, cs.LastSale
, cs.Salesman
, CAST(cs.SaleValue AS NUMERIC)
FROM
dbo.CustomerSales AS cs;
PRINT ‘End TRY’;
END TRY
BEGIN CATCH
PRINT ‘Start CATCH’;
THROW 51000, ‘A calculation error occurred’, 1;
PRINT ‘End CATCH’;
END CATCH;
PRINT ‘End of proc’;
GO
If I not execute this, with a parameter, I get this:
Before TRY
Start TRY
(0 row(s) affected)
Start CATCH
Msg 51000, Level 16, State 1, Procedure spGetCommission, Line 20
A calculation error occurred
That might not be what you expected. The TRY works as expected, with the error in my query sending execution to the CATCH block, before the final print statement in the TRY block.
However I didn’t get the complete execution of the CATCH block, as the THROW throws an error and completes its execution. If I changed this to not re-throw the error, the final statement executes.
ALTER PROCEDURE spGetCommission
…
BEGIN CATCH
PRINT ‘Start CATCH’;
PRINT ‘A calculation error occurred’
PRINT ‘End CATCH’;
END CATCH;
PRINT ‘End of proc’;
GO
In this case, I’ll get all my print statements.
Before TRY
Start TRY
(0 row(s) affected)
Start CATCH
A calculation error occurred
End CATCH
End of proc
A basic look at TRY..CATCH, and worth knowing about. I’d suggest you use this in future code, and even refactor code where you can to include this instead of looking at @@error to trap issues.
Voice of the DBA 