다음을 통해 공유

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

  • Anonymous
    December 15, 2004
    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