views:

1925

answers:

4

We have a large application mainly written in SQL Server 7.0, where all database calls are to stored procedures. We are now running SQL Server 2005, which offers more T-SQL features.

After just about every SELECT, INSERT, UPDATE, and DELETE, the @@ROWCOUNT and @@ERROR get captured into local variables and evaluated for problems. If there is a problem the following is done:

  • error message output parameter is set
  • rollback (if necessary) is done
  • info is written (INSERT) to log table
  • return with a error number, unique to this procedure (positive if fatal, negative is warning)

They all don't check the rows (only when it is known) and some differ with more or less log/debug info. Also, the rows logic is somethimes split from the error logic (on updates where a concurrency field is checked in the WHERE clause, rows=0 means someone else has updated the data). However, here is a fairly generic example:

SELECT, INSERT, UPDATE, or DELETE

SELECT @Error=@@ERROR, @Rows=@@ROWCOUNT
IF @Rows!=1 OR @Error!=0
BEGIN
    SET @ErrorMsg='ERROR 20, '+ISNULL(OBJECT_NAME(@@PROCID), 'unknown')+' - unable to ???????? the ????.'
    IF @@TRANCOUNT >0
    BEGIN 
        ROLLBACK
    END

    SET @LogInfo=ISNULL(@LogInfo,'')+'; '+ISNULL(@ErrorMsg,'')+
        + ' @YYYYY='        +dbo.FormatString(@YYYYY)
        +', @XXXXX='        +dbo.FormatString(@XXXXX)
        +', Error='         +dbo.FormatString(@Error)
        +', Rows='          +dbo.FormatString(@Rows)

    INSERT INTO MyLogTable (...,Message) VALUES (....,@LogInfo)

    RETURN 20

END

I am looking into replacing how we do this with the TRY-CATCH T-SQL. I've read about the TRY...CATCH (Transact-SQL) syntax, so don't just post some summary of that. I'm looking for any good ideas and how best to do or improve our error handling methods. It doesn't have to be Try-Catch, just any good or best practice use of T-SQL error handling.

+10  A: 

You should read this:

http://www.sommarskog.se/error-handling-I.html

I can't recommend that link highly enough. It's a bit long, but in a good way.

There's a disclaimer at the front that it was written for SQL Server 2000, but it covers the new error handling abilities in SQL Server 2005 as well.

Joel Coehoorn
Wow, that is a long article. Thx for the link.
John Dibling
+1 excellent article, thanks for the link!
scraimer
I feel it skips on the SQL Server 2005 stuff, but excellent anyway. And the rest of his site too.
gbn
+2  A: 

It looks like you have a very good handle on this already. I suspect you're doing more than 95% of the SQL programmers out there.

You should find some interesting information here:

One [unrelated] suggestion: start using '<>' instead of '!='.

[* SQL Junkies has gone away, so the second article is not available. I will try to get it republished somewhere and update the link.]

Rob Garrison
why use '<>' instead of '!='?
KM
ANSI spec specifies <>. Many db's also support !=, but it's not standard.
Joel Coehoorn
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt See section 5.2
Joel Coehoorn
There are thousands of stored procedures in my DB used by my application. I use @@ERROR and MANY MANY other T-SQL ONLY features EVERYWHERE. You're even recommending the use of T-SQL only TRY-CATCH. The use of a standard "<>" vs a "!=" is the least of my concerns!
KM
It was just a suggestion. Sheesh.
Rob Garrison
But now that you know, why not do it right?...
Joel Coehoorn
I still see no reason to worry about "<>" or "!=", ever. Despite the SQL standards, I have never worked on any database code that could easily port to another database. They all have their own "extras" that cause problems.
KM
You may need to port your SQL 2000 code to SQL 2005 or SQL 2008. MS has written in Books online that many features are going to be deprecated and eventually removed. Many of the ones on the chopping block are the non-ANSI extensions. That said, I agree, you shouldn't write code expecting to port to, say DB2, because it will never happen.
MatthewMartin
+4  A: 

There isn't a set in stone best practices for Error handling. It all comes down to what your needs are and being consistent.

Here is a sample of a table and stored procedure that stores phone numbers.

 SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Phone](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [Phone_Type_ID] [int] NOT NULL,
        [Area_Code] [char](3) NOT NULL,
        [Exchange] [char](3) NOT NULL,
        [Number] [char](4) NOT NULL,
        [Extension] [varchar](6) NULL,
     CONSTRAINT [PK_Phone] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF
    GO
    /**/

    CREATE PROCEDURE [dbo].[usp_Phone_INS]
         @Customer_ID INT
        ,@Phone_Type_ID INT
        ,@Area_Code CHAR(3)
        ,@Exchange CHAR(3)
        ,@Number CHAR(4)
        ,@Extension VARCHAR(6)
    AS
    BEGIN
        SET NOCOUNT ON;

        DECLARE @Err INT, @Phone_ID INT

        BEGIN TRY
         INSERT INTO Phone
          (Phone_Type_ID, Area_Code, Exchange, Number, Extension)
         VALUES
          (@Phone_Type_ID, @Area_Code, @Exchange, @Number, @Extension)
         SET @Err = @@ERROR
         SET @Phone_ID = SCOPE_IDENTITY()
         /* 
          Custom error handling expected by the application.
          If Err = 0 then its good or no error, if its -1 or something else then something bad happened.
         */
         SELECT ISNULL(@Err,-1) AS Err, @Phone_ID
        END TRY
        BEGIN CATCH
         IF (XACT_STATE() <> 0)
          BEGIN
           ROLLBACK TRANSACTION
          END

         /* 
       Add your own custom error handling here to return the passed in paramters. 
       I have removed my custom error halding code that deals with returning the passed in parameter values.
      */

         SELECT ERROR_NUMBER() AS Err, ISNULL(@Phone_ID,-1) AS ID
        END CATCH
    END
DBAndrew
+1  A: 

We currently use this template for any queries that we execute (you could leave out the Transaction stuff, if you don't need it in e.g. a DDL statement):

BEGIN TRANSACTION
BEGIN TRY
    // do your SQL statements here

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    SELECT 
     ERROR_NUMBER() AS ErrorNumber,
     ERROR_SEVERITY() AS ErrorSeverity,
     ERROR_STATE() AS ErrorState,
     ERROR_PROCEDURE() AS ErrorProcedure,
     ERROR_LINE() AS ErrorLine,
     ERROR_MESSAGE() AS ErrorMessage

    ROLLBACK TRANSACTION
END CATCH

Of course, you could easily insert the caught exception into your error log table.

It works really well for us. You could probably even automate some of the conversion from your old stored procs to a new format using Code Generation (e.g. CodeSmith) or some custom C# code.

Marc

marc_s