Exception Handling – shannonlowder.com

Learning to handle errors better can be one of the skills that will really set you apart from other SQL Developers. You can do constant unit testing after each change, but odds are you’ll never hit ever scenario your code will face. There is a better way.

If you have programmed in any language like PHP, C#, etc, I’m sure you’ve used the TRY..CATCH construct. If not, you’re in for a treat. This construct can actually catch once-fatal errors, and allow you to handle them cleanly, without passing the error along to your user interface. Here’s the basic template.

BEGIN TRY
  ..T-SQL statements that could fail (aka, ALL of your code!)..
END TRY
BEGIN CATCH
  ..What do you want to do in case you do have an error?..
END CATCH

Let’s use a really simple example. We’re going to build a function called divide. I know we already have one… but we’re building one that has error handling built in.

IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'divide')
  DROP PROCEDURE dbo.divide
GO
CREATE PROCEDURE divide (
	  @numerator DECIMAL(9,2)
	, @denominator DECIMAL(9,2)
	, @result DECIMAL(9,2) OUTPUT
)
RETURNS DECIMAL(9,2)
BEGIN
	SET @result = @numerator / @denominator
END

The function does exactly what you want it to do, divides the numerator by the denominator. But what if someone passes 0 for the denominator? You get and error

Msg 8134, Level 16, State 1, Line 3
Divide by zero error encountered.

Not pretty.

But, using the TRY..CATCH, we can fail gracefully.

IF EXISTS ( SELECT name FROM sysobjects WHERE name = 'divide')
  DROP PROCEDURE dbo.divide
GO
CREATE PROCEDURE divide
	  @numerator DECIMAL(9,2)
	, @denominator DECIMAL(9,2)
	, @result DECIMAL(9,2) OUTPUT

AS
BEGIN
	BEGIN TRY
		SET @result = @numerator / @denominator
	END TRY
	BEGIN CATCH
		PRINT 'You had an error, did you try to divide by zero?'
		SET @result = 0
	END CATCH
END

Now, if it fails, you get a pretty error, and the result comes back 0. In future posts, I’ll build on the TRY..CATCH construct. You can do more with this, you can detect for certain error codes, you can get the error the line number occurred on, and more! (Think about coupling this with TRANSACTION…imagine the possibilities!) But this is enough for today. Master this much, and I’ll get you a new sword!

If you have any questions, just let me know. I’m here to help!

Leave a Reply Cancel reply