What is Try/Catch?

Try/Catch is something you will also find in other Programming Language like Javascript or C#. Of course the syntax might be different for each Language. But the use of it is similar in every Language.

First you have the Try which has a Begin and End. You can put any code bwtween the Begin and End, or in the case of SQL any Query in there. If anything goes wrong between the Begin and End, that part will fail from that point onwards. But your Query will continue like nothing happened after the End.

The Try is used in combination with the Catch which also has a Begin and End. If all goes well, the Code inside the Try will not be executed. If something goes wrong inside the Try, the Catch will be fired.

So if there is an Error, you can use the Catch to take action. You could log the Error or, more typical, you could use a RollBack the Transaction.

Especially the use of a Transaction is very helpful when used inside a Try/Catch. For example, in your Stored Procedure you want to first insert a new Header Row in your SalesOrders Table. After that, you continue and Insert the Lines in your SalesOrderLines Table.

Now imagine that something goes wrong with inserting the Lines. With no error handling, you would end up with a Header Row with no corresponding Lines.

With a Try/Catch and a Transaction you can avoid this. And if you use the Catch also to log the error, you can easily identify that something went wrong.

A basic Try/Catch

A basic Try/Catch would look like this:

begin try
  begin tran
    select 1 / 0
  commit tran
end try
begin catch
  rollback tran

  select
  error_message() as ErrorMessage,
  error_number() as ErrorNumber,
  error_state() as ErrorState,
  error_severity() as ErrorSeverity,
  error_procedure() as ErrorProcedure,
  error_line() as ErrorLine
end catch
GO

In this Query there is a Try which starts with Begin Try and ends with End Try. Inside of this I used a Begin Tran and an Commit Tran. Inside of that one I put a Query that is surely going to fail since we cannot divide by zero.

Of course you would have more elaborate Code inside the Begin and Commit Tran. But I kept it simple here because it only serves to explain the use of a Try/Catch.

Without the Try/Catch the Query would run into the Error, stop at that Line and throw you the Error. Using a Try/Catch this is not the case.

Sure, the Query inside the Try will still fail, but it will not throw an Error. And since the Error happens on the division by zero Line, the next Line (the Commit Tran) will not be executed.

Instead, from that point the Query will jump to the Catch section and execute what is inside of that. So in this case I used the Catch to RollBack the Transaction so nothing will happen.

After the RollBack you could also do some Error Handling. For example, you could log the Error that occured.

Catch the Content of the Error

The Catch has some build-in Functions that contain information you might want to use to Log what happened. The most usefull one is the error_message() which will give you the Error like you would see if you would run the Query without a Try/Catch.

If you use a Try/Catch inside a Stored Procedure, the error_procedure() can be pretty useful as well. It shows you the name of the Procedure in which you used the Try/Catch. This one can be very helpful when you log the Error so you know which Procedure is not working as expected.

You can see it for yourself with this one:

create or alter Procedure TryCatchTest as
begin try
  begin tran
    select 1 / 0
  commit tran
end try
begin catch
  rollback tran

  select
  error_message() as ErrorMessage,
  error_number() as ErrorNumber,
  error_state() as ErrorState,
  error_severity() as ErrorSeverity,
  error_procedure() as ErrorProcedure,
  error_line() as ErrorLine
end catch
GO

TryCatchTest

With the first Query, the ErrorProcedure was null, but with this one it shows you something went wrong inside the TryCatchTest. And that is useful information if you want to do some troubleshooting.

Common Practice

Usually when I use Stored Proecures that are scheduled to run in the background using the SQL Agent, I do some logging. For that I create a Table like ProcedureLogs with a RunID to keep the Actions from the same Run together.

Besides that, I log which Procedure was running and a Created DateTime to know when the Procedure was running. Last Column would be a LogMessage showing what step of the Procedure was executed. Sometimes I add the number of rows found for a Select Statement, or the number of rows Inserted, Updated or Deleted.

If something fails and I end up in the Catch, I use the LogMessage to store the error_message().

A bit of caution though

Using a Try/Catch is great way to suppress any unexpected error. So it looks like everything works fine.

But is that really the case?

It could be something went horrably wrong, which you will not notice if you don't do the approriate error handling. Use the Try/Catch wisely and sparesly. Don't just use it because you can to cover up some sloppy code.

That's it for Today!

As you can see the Try/Catch can be pretty useful to prevent a single Error to break the entire Query you are running. Together with Begin Tran and End Tran, you can prevent that something is already Inserted, Updated or Deleted while the second part will not happen because of an unexpected Error. Simply do a RollBack inside the Catch.

And the Try/Catch is also helpful to log what is happening since it will give you the Error Message you would have gotten on your screen.

Just be sure to handle the Error in the Catch!

Yes, you could use a Try also to suppress any Error that might occur and then just ignore that. But that is a bad practice you should avoid.

Just because no Error occured, it doesn't mean that everything went well.

You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!