More ways to stop errors

Errors can be the bane of a developer’s existence. They can stop even the best developer’s work. A process that takes several hours can be stopped dead in it’s tracks in the last hour because of a horrible error.

Thankfully, last time, we discussed one way to make a stored procedure more aware of errors and able to combat against them – the mighty RAISERROR command. Now, the RAISERROR command has been around since SQL Server 7.0 and requires the developer to do some extra work at times.

Starting in SQL Server 2012, we began to be able to able to use a TRY-CATCH block, something that had existed in some other programming languages since the early 1970s! In the SQL Server implementation, there are 3 words that you’ll need to remember – TRY, CATCH, and THROW.

BEGIN TRY

	SELECT @Numerator / @Denominator

END TRY
BEGIN CATCH

	THROW

END CATCH

Sticking with our original stored procedure from last time, we can now change to this “simple” framework and it does a lot of the work for us. And with this implementation, we get an error message that is somewhat helpful!

Now, this is where the TRY-CATCH block can come in helpful for those long, arduous stored procedures. If you leave out the THROW statement, and have the stored procedure just write to an error log, the entire stored procedure can end gracefully.

Now, obviously, that can lead to other dangers – like finding out that your process has been writing to the error log every day for the last 6 months and no one has checked in on it – but that’s definitely not a topic for today. (Just think of happy thoughts if this happens to you. Breathe. Just happy thoughts.)