transaction - ¿Cuál es la mejor práctica de uso del manejo de errores de SQL Server T-SQL?

sql server raiserror try catch

Question

Tenemos una gran aplicación escrita principalmente en SQL Server 7.0, donde todas las llamadas de bases de datos son a procedimientos almacenados. Ahora estamos ejecutando SQL Server 2005 , que ofrece más características de T-SQL.

Después de cada SELECCIONAR, INSERTAR, ACTUALIZAR y BORRAR, @@ ROWCOUNT y @@ ERROR se capturan en las variables locales y se evalúan los problemas. Si hay un problema se hace lo siguiente:

• el parámetro de salida del mensaje de error está configurado
• se hace rollback (si es necesario)
• La información se escribe (INSERTAR) en la tabla de registro
• devuelva con un número de error, exclusivo de este procedimiento (positivo si es fatal, negativo es advertencia)

Todos no verifican las filas (solo cuando se sabe) y algunos difieren con más o menos información de registro / depuración. Además, la lógica de las filas a veces se divide de la lógica de error (en las actualizaciones donde se comprueba un campo de concurrencia en la cláusula WHERE, las filas = 0 significa que alguien más ha actualizado los datos). Sin embargo, aquí hay un ejemplo bastante genérico:

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

Estoy buscando reemplazar cómo hacemos esto con el TRY-CATCH T-SQL. He leído sobre la sintaxis TRY ... CATCH (Transact-SQL) , así que no solo publique un resumen de eso. Estoy buscando buenas ideas y la mejor manera de hacer o mejorar nuestros métodos de manejo de errores. No tiene que ser Try-Catch, solo un uso bueno o recomendado del manejo de errores T-SQL.

Answer 1

Actualmente utilizamos esta plantilla para cualquier consulta que ejecutemos (puede omitir el contenido de Transacción, si no lo necesita, por ejemplo, en una declaración DDL):

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

Por supuesto, puede insertar fácilmente la excepción capturada en su tabla de registro de errores.

Funciona muy bien para nosotros. Probablemente podría incluso automatizar algunas de las conversiones de sus antiguos procesos almacenados a un nuevo formato utilizando la Generación de Código (por ejemplo, CodeSmith) o algún código C # personalizado.

Answer 2

Deberías leer esto:

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

No puedo recomendar ese enlace lo suficiente. Es un poco largo, pero de buena manera.

Hay un descargo de responsabilidad en el frente que originalmente fue escrito para SQL Server 2000, pero también cubre las nuevas capacidades de manejo de errores de prueba / captura en SQL Server 2005+.

Answer 3

No hay un conjunto de mejores prácticas para el manejo de errores. Todo se reduce a cuáles son tus necesidades y a ser coherente.

Aquí hay una muestra de una tabla y un procedimiento almacenado que almacena números de teléfono.

 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

Answer 4

Parece que ya tienes un buen manejo de esto. Sospecho que estás haciendo más del 95% de los programadores de SQL por ahí.

Deberías encontrar alguna información interesante aquí:

• Detección e informe de errores en procedimientos almacenados - Parte 1: SQL Server 2000
• Detección e informe de errores en procedimientos almacenados - Parte 2: Bloques TRY-CATCH de SQL Server 2005 *

Una sugerencia [no relacionada]: comience a usar ''<>'' en lugar de ''! =''.

[* SQL Junkies ha desaparecido, por lo que el segundo artículo no está disponible. Intentaré republicarlo en alguna parte y actualizar el enlace.]