How about we TRY and CATCH some Transactions?

In our last post, we started talking about transactions and how they can help your databases have data integrity. Let’s continue that today with our friends TRY and CATCH.

TRY is just telling SQL Server that we want to try out something. CATCH tells SQL Server what to do if it completely messes up.

BEGIN TRY
-- Generate a divide-by-zero error  
  SELECT
    1 / 0 AS Error;
END TRY
BEGIN CATCH
  SELECT
    ERROR_NUMBER() AS ErrorNumber,
    ERROR_STATE() AS ErrorState,
    ERROR_SEVERITY() AS ErrorSeverity,
    ERROR_PROCEDURE() AS ErrorProcedure,
    ERROR_LINE() AS ErrorLine,
    ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

We know – or at least we should know – that the SELECT 1/0 will error out. In the CATCH block, it will return a message of what all went wrong with the code in the TRY block.

Amazingly, we get the following error:

Now, to come back to transactions, we can create something like the following:

SELECT *
INTO dbo.Person4
FROM dbo.Person1
WHERE 1=0

BEGIN TRY
    BEGIN TRAN
        INSERT INTO dbo.Person4
        (FirstName, LastName)
        SELECT 'Fred', 'Flintstone'

    COMMIT
END TRY
BEGIN CATCH
   DECLARE @Message varchar(MAX) = ERROR_MESSAGE(),
        @Severity int = ERROR_SEVERITY(),
        @State smallint = ERROR_STATE()
 
   RAISEERROR (@Message, @Severity, @State)
   IF @@TRANCOUNT > 0 ROLLBACK
END CATCH

In the topmost SELECT statement, I’m just creating a new dbo.Person4 table that matches the dbo.Person1 table’s DDL.

I then start the TRY block and then the TRANSACTION. I attempt to insert some data into the new dbo.Person4 table. If that insert errors out, then it would go to the CATCH block and show an error message. If everything goes smoothly, then the transaction is committed and we’re all good.

One of the big points to remember with a transaction though, is that you should make sure to keep it open only long enough to do what you need to – and no longer. Don’t try to do any hard calculations. Don’t try to join multiple tables together. Just shove the data into a table and close it quickly. Trust me, your users will thank you for being done quickly.

And so, with that, we have come to the end of another blog post. Hopefully, you have learned something about transactions and how to use them with the TRY/CATCH block. Next time, we’ll learn a little bit more about transactions and some items you can do with them. Until then my friends….

Just remember that CATCH will catch you like Demi Lovato in today’s song.