Fotia Ltd

Fotia Ltd

Friday, 21 April 2006

TRY...CATCH

posted by Stefan Delmarco

I'll start off by saying that I am not a fan of the error handling practises that this syntax promotes. People are often surprised when I tell them that, in my opinion, BEGIN TRAN, ROLLBACK TRAN, SELECT @@ERROR, etc. do not belong in stored procedures. Think of it this way, have you ever seen any code in a stored procedure that actually does anything useful when an unexpected error occurs, besides exiting the stored procedure? Then why do we have to have the standard 10 lines of boiler plate 'error handling' TSQL that:

  • tests @@ERROR after every DML statement,
  • does a GOTO to an ErrorHandler label, and
  • does a RAISERROR of 'an unexpected error occurred'?

If the intention is just to stop when an error occurs then the SET XACT_ABORT statement is all that is needed at the start of the batch / connection. In addition, if you're using .NET then you have the SqlException class available. This exception class (which gets thrown by the SqlConnection class when a warning / error occurs) includes the name of the procedure and the line number the error occurred on. What more do you need? If you need additional context, like a customer ID, you probably know that already in the middle-tier / client code that is calling the stored procedure. Alternatively, the source of the error is a business rule that has failed and you're performing a custom RAISERROR anyway and are able to substitute values, like the customer ID, into the error message.

Ultimately, the entity in charge of the transaction must be the arbiter that decides whether the transaction needs to commit or rollback. This has to be the middle-tier code that is calling the stored procedure and not the stored procedure itself. The only situation I can think of where transaction control logic needs to reside in TSQL is when the entity in charge is Query Analyser / SQL Server Management Studio (SSMS), i.e. you're running SQL scripts and don't have the luxury of C# available and you need some actions to be performed if an error occurs. Likewise a stored procedure that will act as a Service Broker internal service program could benefit from TRY...CATCH as the stored procedure is the initiator.

Anyway, enough of my opinions. I'll be covering recommended error handling practises in a later instalment. Let's look at what TRY...CATCH brings to the table.

As you've probably guessed, TRY..CATCH is a TSQL implementation of the exception handling semantics we've grown to love in .NET / C++. A TRY block determines the scope of the TSQL statements that will be monitored for RAISERRORs (caused by either SQL Server or custom RAISERRORs). If an error is raised the TRY block is immediately exited and control is passed to the corresponding CATCH block. TRY..CATCH blocks can be nested in the same manner as C#. However, there is no concept of a FINALLY block. The following TSQL is an example of a simple TRY...CATCH block.

begin try
    -- Fails with a primary key violation
    insert Sales.Currency (CurrencyCode, [Name])
    values (N'GBP', N'United Kingdom Pound')

    print N'Will not be reached...'
end try
begin catch

    print N'In catch block...'
end catch

Running this in SSMS produces the following output:

In catch block...

The good thing about this behaviour is that we no longer need to include the standard @@ERROR test after any DML. In fact, if the code is within a TRY block we'll never get a chance to test @@ERROR anyway. 'Will not be reached...' is never printed. Also note that SSMS did not report any error. What? Where has my PK violation gone? To ensure that I wasn't seeing a quirk of SSMS I ran the following in a C# console application:

try
{
    using(SqlConnection con = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=SSPI"))
    using SqlCommand cmd = con.CreateCommand())
    {
        con.Open();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = @"
begin try
    -- Fails with a primary key violation
    insert Sales.Currency (CurrencyCode, Name)
    values (N'GBP', N'United Kingdom Pound')

    print N'Will not be reached'
end try
begin catch
    print N'In catch block'
end catch"
;
        cmd.ExecuteNonQuery();
        Console.WriteLine("It all worked perfectly...");
    }
}
catch(Exception exception)
{
    Console.WriteLine(exception);
}

Again, no exception. So the TSQL CATCH is behaving exactly like exceptions in .NET. If you catch an exception in TSQL and don't re-raise the exception, the caller will never know that it occurred. Aaargh! Why would you ever want that behaviour in a middle-tier application? The golden rule with exception handling is: "Don't catch exceptions unless you can do something about them". When could you ever do something about an error in TSQL besides aborting the batch?

Anyway, if you ever decide to use TRY..CATCH in TSQL there are a couple of new functions that are available to inspect the exception, think of them as @@ERROR replacements. You now have ERROR_LINE(), ERROR_MESSAGE(), ERROR_NUMBER(), ERROR_PROCEDURE(), ERROR_SEVERITY(), ERROR_STATE() available inside the catch block. In addition, there is another function called XACT_STATE() available that tells you whether or not the transaction is capable of being committed. BOL states that if XACT_STATE returns -1 then an error has occurred in the active transaction that has rendered it uncommittable. In this state only read operations are allowed. A transaction will enter this state if XACT_ABORT has been set on and an error was raised.

I can see how TRY..CATCH will be useful if you are running pure TSQL scripts. However, for any database interactions that are initiated from application servers / clients I don't believe they should be used at all. One of the holy grails I rely on is that, in SQL Server 2000, no errors can ever be swallowed / suppressed in the database. The client always has full knowledge of all errors that occurred, even if there were multiple RAISERRORs (SQL Server initiated and / or custom errors - SqlException has a SqlErrors collection). Use of TRY..CATCH in these cases could hide these errors from the application server / client resulting in mysterious missing data / errors that the caller has no knowledge of. I feel that the use of TRY..CATCH would be on par with the evil that is catch(...) in C++!

I had a colleague suggest that as the new TRY...CATCH syntax can catch deadlocks (which are not possible to handle within TSQL in SQL Server 2000), you could move the deadlock retry code from the application servers into the database. Please don't do this unless you want applications that are randomly slow with transactions boundaries as fuzzy as Beyonce's afro!

Next up, SNAPSHOT_ISOLATION level for all the Oracle pundits.

Labels: ,

Previous Posts