Error handling in Microsoft SQL stored procedures is gross!


I’m doing some SQL stored procedure programming for the first time. The error handling is really, really primitive. T-SQL uses lots of global variables, which makes things really complex. For example, if you do an UPDATE, @@ROWCOUNT has the number of rows affected, and @@ERROR has the error code. However, if you look at @@ROWCOUNT, you’ll reset @@ERROR. And, if you look at @@ERROR, you reset @@ROWCOUNT! What can you do? Well, you have to put both values into temporary variables in a single statement:

DECLARE @err int,

        @rowcount int

UPDATE

SELECT @err = @@ERROR, @rowcount = @@ROWCOUNT

Now, you can look at the local variables @err and @rowcount. Erland Sommarskog has a couple of great articles on SQL error handling here and here.


Comments (1)

  1. Steve says:

    Yeah totally agree – at least Yukon introduces exception handling to help out on creating some structure to managing exceptions in code path. e.g.

    BEGIN TRY

    sql statement

    END TRY

    BEGIN CATCH TRAN_ABORT

    sql statement

    END CATCH