What is the best practice use of SQL Server T-SQL error handling? [closed]

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 originally written for SQL Server 2000, but it covers the new try/catch error handling abilities in SQL Server 2005+ as well.

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 TRANSACTIONBEGIN TRY    // do your SQL statements here    COMMIT TRANSACTIONEND TRYBEGIN 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 TRANSACTIONEND 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.

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