Gestión de errores en SQL Server

Si te toca escribir “algo” de lógica de negocio en T-SQL, y te agobia la gestión de errores (como a mi), espero que después de este post tengamos los 2 las cosas un poco más claras.

La primera opción que para tratar errores es con el estilo old-school, es decir, con @@ERROR, que devuelve un número de error si la última sentencia T-SQL ejecutada dio algún error, devolverá 0 si no hubo ningún error.

Para todos los ejemplos vamos a usar una tabla con una sola columna.

    CREATE TABLE Table1 (Id INT PRIMARY KEY)
    

Usando @@ERROR

    DELETE FROM Table1;
    GO
    INSERT INTO Table1 VALUES (1);
    INSERT INTO Table1 VALUES (1);
    IF @@ERROR <> 0
        PRINT 'There was an error';
    INSERT INTO Table1 VALUES (2);
    GO
    SELECT COUNT(*) FROM Table1;
    

Lo más relevante de este código es que, finalmente, la tabla tiene 2 registros, es decir, a pesar del error de la línea 4, el resto del script se ha seguido ejecutando. Este comportamiento de seguir ejecutando el script es el predeterminado, pero ¿qué pasa si no quiero que sea así? Pues podemos usar XACT_ABORT, además @@ERROR no parece una técnica muy segura.

Si XACT_ABORT es ON, en caso de haber un error, se acaba la ejecución inmediatamente del lote y se revierte, si la hubiera, la transacción explícita. Si es OFF, el valor predeterminado, pues funciona como el anterior script, la ejecución sigue y no se revierte automáticamente ninguna transacción explícita (sólo la implícita que es la propia sentencia).

        SET XACT_ABORT ON;
        DELETE FROM Table1;
        GO
        INSERT INTO Table1 VALUES (1);
        INSERT INTO Table1 VALUES (1);
        INSERT INTO Table1 VALUES (2);
        GO
        SELECT COUNT(*) FROM Table1;    
        

Es decir, la instrucción 6 no se ejecuta porque se aborta la ejecución del lote, por eso finalmente, sólo hay 1 registro en la tabla destino.

Antes de ver como XACT_ABORT ON revierte automáticamente una transacción explícita, es importante conocer la función XACT_STATE. Esta función nos devuelve un valor que indica si hay o no una transacción explícita y en que estado está.

• 1. Hay transacción.
• 0. No hay transacción.
• -1. Hay transacción, pero un error hizo que la transacción no se pueda confirmar. La única operación válida es deshacer toda la transacción.

Las diferencias entre @@TRANCOUNT y XACT_STATE es que @@TRANCOUNT permite saber si hay transacciones anidadas y XACT_STATE permite saber si la transacción es confirmable.

        SET XACT_ABORT ON;
        GO
        DELETE FROM Table1;
        GO
        BEGIN TRAN;
        INSERT INTO Table1 VALUES (1);
        INSERT INTO Table1 VALUES (1);
        INSERT INTO Table1 VALUES (2);
        COMMIT TRAN;
        GO
        PRINT XACT_STATE();
        PRINT @@TRANCOUNT;
        IF XACT_STATE() = 1
            COMMIT TRAN;
        IF XACT_STATE() = -1
            ROLLBACK TRAN;
        GO
        SELECT * FROM Table1;    
        

Como la línea 7 da un error y XACT_ABORT es ON, pasa lo siguiente:

• Se aborta la ejecución del lote, no se ejecuta la línea 8.
• Automáticamente se revierte la transacción explícita. Luego XACT_STATE y @@TRANCOUNT pasan a valer 0.

Si comentáramos la línea 7, XACT_STATE valdría 1 y se ejecutaría COMMIT TRAN.

La línea 16 hace ROLLBACK TRAN si por algún motivo la transacción se volvió no confirmable.

De nuevo, antes de seguir es necesario entender otro concepto, como maneja los timeouts de cliente SQL Server. Un timeout de cliente es como si pulsáramos “Cancel Executing Query” en SSMS, el botón Stop, vamos. Por ejemplo, creamos un procedimiento almacenado como el siguiente:

        CREATE PROCEDURE Foo
        AS
        PRINT 'Sergio';
        WAITFOR DELAY '00:00:10';
        PRINT 'panicoenlaxbox';
        END    
        

Si lo ejecutamos y antes de que pasen 10 segundos pulsamos Stop, sólo veremos la salida 'Sergio', es decir, se deja de ejecutar el script y no vemos 'panicoenlaxbox'.

¿Desde una aplicación cliente funcionará igual?

Lo primero es poder ver PRINT en el SQL Server Profiler, esto no es necesario para comprobar esto, pero me parece útil poder ver PRINT en el Profiler, lo he sacado de aquí. Creamos el procedimiento almacenado que hace la magia:

        CREATE PROCEDURE PrintTrace1
        @Text nvarchar(max) 
        AS
        BEGIN
        DECLARE @UserData BINARY(8000) = 0
        DECLARE @UserInfo NVARCHAR(256) = SUBSTRING(@Text,1,256)
        PRINT   @Text
        EXEC sp_trace_generateevent 82, @UserInfo, @UserData
        END    
        

Y modificamos el anterior procedimiento para lo use:

    ALTER PROCEDURE Foo
    AS
    EXEC PrintTrace1 'Sergio';
    WAITFOR DELAY '00:00:10';
    EXEC PrintTrace1 'panicoenlaxbox';    
    

Por último, cuando abramos el Profiler será necesario marcar el evento UserConfigurable:0 para ver la salida de PrintTrace1.

Ahora nuestro código cliente:

    using System;
    using System.Data;
    using System.Data.SqlClient;
    
    namespace ConsoleApp1
    {
        class Program
        {
            static void Main(string[] args)
            {
                try
                {
                    using (var connection = new SqlConnection(@"Server=(LocalDB)\MSSQLLocalDB;Database=Sergio;Trusted_Connection=True;"))
                    {
                        connection.Open();
                        using (var command = connection.CreateCommand())
                        {
                            command.CommandTimeout = 5;
                            command.CommandType = CommandType.StoredProcedure;
                            command.CommandText = "Foo";
                            command.ExecuteNonQuery();
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);                
                }
                Console.ReadKey();
            }
        }
    }    
    

Con CommandTimeout 5 y WAITFOR DELAY '00:00:10' el timeout está garantizado, devolviendo el error típico al cliente Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding, y confirmando que igualmente deja de ejecutar el resto de script en el servidor:

¿Y todo esto del timeout por qué? Pues porque ahora vamos a ver TRY…CATCH de SQL Server, manejado estructurado de errores, buena cosa, pero era necesario tener los anteriores conceptos claro para poder hablar sobre ellos.

De TRY…CATCH el ejemplo típico es el siguiente, donde al igual que pasaba con XACT_ABORT ON, ahora cuando se sucede un error dentro del bloque TRY, la ejecución no continua, sino que salta al bloque CATCH.

        DELETE FROM Table1;
        GO
        BEGIN TRY
            BEGIN TRAN;
                INSERT INTO Table1 VALUES (1);
                -- RAISERROR with severity 11-19 will cause execution to jump to the CATCH block.  
                --RAISERROR ('Error raised in TRY block.', -- Message text.  
                --		   16, -- Severity.  
                --		   1 -- State.  
                --		   ); 
                INSERT INTO Table1 VALUES (1);
                INSERT INTO Table1 VALUES (2);
            COMMIT TRAN;
        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;
            IF XACT_STATE() <> 0 
            BEGIN
                PRINT 'ROLLBACK TRAN';
                ROLLBACK TRAN;
            END
        END CATCH
        GO
        SELECT * FROM Table1;   
        

¿Cómo se llevará TRY…CATCH con un timeout?

Si hay un error de timeout no se ejecutará el CATCH, es decir, no creas que siempre que si hay un TRY…CATCH el CATCH siempre está asegurado.

Modificando el procedimiento anterior y cancelando la ejecución desde SSMS vemos este comportamiento.

    ALTER PROCEDURE Foo
    AS
        DELETE FROM Table1;
        BEGIN TRY
            BEGIN TRAN;
                INSERT INTO Table1 VALUES (1);
                EXEC PrintTrace1 'waitfor...';			
                WAITFOR DELAY '00:00:10';
                EXEC PrintTrace1 'continue...';
                INSERT INTO Table1 VALUES (1);
                INSERT INTO Table1 VALUES (2);
            COMMIT TRAN;
        END TRY
        BEGIN CATCH
            EXEC PrintTrace1 'catch...';
            IF XACT_STATE() <> 0 
            BEGIN
                PRINT 'ROLLBACK TRAN';
                ROLLBACK TRAN;
            END
        END CATCH 
    

Y ahora la pregunta es: Si no puedo garantizar la ejecución del bloque CATCH, ¿debería activar siempre XACT_ABORT para garantizar que la transacción explícita siempre se rechazara automáticamente? Pues parece que sí, porque quien defiende XACT_ABORT lo hace porque si no está activo, un timeout de cliente podría dejar la conexión con recursos bloqueados hasta que la transacción se cancele o la conexión se cierre, y asumiendo que hay pool de conexiones, un mal código de cliente podría arruinar el servidor, incluso en un comentario del mismo post queda clara la jugada.

Por otro lado, si activamos XACT_ABORT y además hay un TRY…CATCH, el CATCH seguirá ejecutándose, pero XACT_STATE valdrá -1 (la única operación válida es deshacer la transacción) y además @@TRANCOUNT seguirá valiendo lo que valía, es decir, un -1 en XACT_STATE no rechaza automáticamente la transacción explícita.

Y antes de llegar a nuestro snippet definitivo para la gestión de errores, hablemos de transacciones anidadas en SQL Server. Poder se puede:

        BEGIN TRAN
            BEGIN TRAN
            PRINT @@TRANCOUNT --2
            COMMIT TRAN
            PRINT @@TRANCOUNT --1
        COMMIT TRAN
        PRINT @@TRANCOUNT --0    
        

Además, aparece el concepto de salvar una transacción que lo que permite es deshacer partes concretas de una transacción. Cabe mencionar que SAVE TRAN no incrementa @@TRANCOUNT y, por ende, ROLLBACK TRAN <nombre> tampoco lo decrementa.

        BEGIN TRAN
            BEGIN TRAN
            PRINT @@TRANCOUNT --2
            SAVE TRAN st1
                --Do something that can be rolled back
                PRINT @@TRANCOUNT --2
                ROLLBACK TRAN st1
            COMMIT TRAN
            PRINT @@TRANCOUNT --1
        COMMIT TRAN
        PRINT @@TRANCOUNT --0        
        

Un ROLLBACK TRAN (sin nombre) deshace todas las transacciones (anidadas también si las hubiera) y decrementa @@TRANCOUNT a 0. ROLLBACK TRAN <nombre> sólo es válido si <nombre> es un SAVE TRAN o un BEGIN TRAN <nombre> siendo esa transacción la más externa (no siendo anidada).

Un COMMIT TRAN (sin nombre) confirma la transacción actual según su nivel de indentación, aunque es válido un COMMIT TRAN <nombre> refiriéndose tanto a una transacción anidada como a una externa.

Como resumen, con transacciones anidadas podemos o bien rechazar todas las transacciones (ROLLBACK TRAN o ROLLBACK TRAN <nombre_de_la_más_externa>) o bien rechazar partes de una transacción anidada (SAVE TRAN <nombre> y ROLLBACK <nombre>).

Y en este momento, es cuando vemos la plantilla de un procedimiento almacenado que he sacado de este post donde le agregamos XACT_ABORT ON para que un timeout de cliente no nos de guerra.

        CREATE PROCEDURE [ProcedureName]
        AS
        BEGIN
            SET NOCOUNT ON;
            SET XACT_ABORT ON;
            DECLARE @trancount INT;
            SET @trancount = @@TRANCOUNT;
            BEGIN TRY
                IF @trancount = 0
                    BEGIN TRANSACTION;
                ELSE
                    SAVE TRANSACTION ProcedureName;
                -- Do something...	
                
                IF @trancount = 0	
                    COMMIT;
            END TRY
            BEGIN CATCH
                DECLARE @errorNumber INT, @message NVARCHAR(4000), @xact_state INT;
                SELECT @errorNumber = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xact_state = XACT_STATE();
                IF @xact_state = -1
                    ROLLBACK;
                IF @xact_state = 1 AND @trancount = 0
                    ROLLBACK;
                IF @xact_state = 1 AND @trancount > 0
                    ROLLBACK TRANSACTION ProcedureName;
        
                RAISERROR('ProcedureName: %d: %s', 16, 1, @errorNumber, @message) ;
            END CATCH
        END   
        

Ahora sí, podemos escribir un “poco” de lógica de negocio en T-SQL con una estrategia clara de gestión de errores.