TRY CATCH

What is try-catch?

Those of you who write code know trycatch blocks for exception handling. Constructs like this one, by which you can react to exceptions instead of simply running on errors and aborting the code, are common in the programming world, but not provided for in the SQL standard.

What can you use try-catch for?

Trycatch blocks are useful in SQL, just like in programming, whenever you want to react to errors in a special way without your code ending with an error. I often use trycatch blocks when I want to create and clean up a (temporary) table for a demo or test. In the past my code to remove temporary tables looked like this:

IF OBJECT_ID('tempdb..#OrdersTemp', 'U') IS NOT NULLBEGIN DROP TABLE #OrdersTemp;END
SELECT
     O.[OrderID]
    ,O.[OrderDate]
    ,O.[ExpectedDeliveryDate]
    ,Ol.[StockItemID]
    ,Ol.[Description]
    ,Ol.[Quantity]
    ,Ol.[UnitPrice]
INTO #OrdersTemp
FROM[Sales].[Orders] AS O
LEFT JOIN[Sales].[OrderLines] AS Ol ON O.[OrderID] = Ol.[OrderID]

And to be honest, I could never get used to the tempdb with the two dots when querying the object ID (and had to read the syntax again for this post). Life became much easier with SQL Server 2016, when Microsoft decided to introduce a drop if exists syntax in SQL Server, which has been possible in other systems for quite some time. After that my queries looked like this:

DROP TABLE IF EXISTS #OrdersTemp
SELECT
     O.[OrderID]
    ,O.[OrderDate]
    ,O.[ExpectedDeliveryDate]
    ,Ol.[StockItemID]
    ,Ol.[Description]
    ,Ol.[Quantity]
    ,Ol.[UnitPrice]
INTO #OrdersTemp
FROM
[Sales].[Orders] AS O
LEFT JOIN
[Sales].[OrderLines] AS Ol ON O.[OrderID] = Ol.[OrderID]

But if you occasionally jump between the versions, you (like me) may find it difficult to remember which of the two versions to use. My queries therefore mostly look like this:

BEGIN TRY DROP TABLE #OrdersTemp END TRY BEGIN CATCH END CATCH
SELECT
     O.[OrderID]
    ,O.[OrderDate]
    ,O.[ExpectedDeliveryDate]
    ,Ol.[StockItemID]
    ,Ol.[Description]
    ,Ol.[Quantity]
    ,Ol.[UnitPrice]
INTO #OrdersTemp
FROM
[Sales].[Orders] AS O
LEFT JOIN
[Sales].[OrderLines] AS Ol ON O.[OrderID] = Ol.[OrderID]

As a person with a programming background it is always the easiest for me to remember a trycatch syntax… Of course, you can do much more exciting things with tryCatch blocks, for example, react to errors in stored procedures and not ignore errors (as in this example) but instead handle them cleanly and keep a log for failed stored procedures.

References

Ninja-Notebooks @ GitHub

2 thoughts on “TRY CATCH

  1. TRY..CATCH ist tatsächlich eines der Konstrukte, wo ich immer wieder spicken muss. Gebe ich gerne zu.
    Spanned ist ja eigentlich, was man noch alles im CATCH Block abfangen kann oder wie es ausschaut, wenn ich TRY..CATCH mit dem Thema explizite Transaktion kombiniere.

    Vielleicht haut der Ninja ja irgendwann dazu auch noch mal was raus 🙂

Leave a Reply to Dirk Hondong Cancel reply

Your email address will not be published. Required fields are marked *