Blog gratis
Reportar
Editar
¡Crea tu blog!
Compartir
¡Sorpréndeme!
img
img

 

 

SQL Server
Blog. (Apuntes y Recopilaciones) por Norman M. Pardell

img
img
17 de Junio, 2011 · ERROR-TRY-CATCH

Control de errores en transacciones. @@ERROR. TRY…CATCH. SQL Server

 El uso de @@ERROR como medio principal de detección de errores conduce a un código de gestión de errores de estilo muy diferente del que se utiliza con construcciones TRY…CATCH.

He de mencionar por encima, aunque no sea el tema, que en  SQL si ejecutamos la instrucción BEGIN TRANSACTION es el originador de la transacción que controla la sentencias que ejecutamos a continuación, cuando posteriormente, en la misma sesión se ejecuta una instrucción COMMIT TRANSACTION o ROLLBACK TRANSACTION, se harán efectivas en disco o retrocederan indistintamente las transacciones.

 @@ERROR debe comprobarse o guardarse después de cada instrucción de Transact-SQL porque un programador no puede predecir qué instrucción puede generar un error. Esto dobla el número de instrucciones de Transact-SQL que deben codificarse para implementar un fragmento de lógica dado. Podemos usar directamente @@ERROR o cargarla en una @variable para su posterior tratamiento, vamos a ver un par de ejemplos. Es interesante cargar el valor de @@ERROR en una @variable, justo después de la consulta que deseamos verificar si se ha producido error, ya que si no lo hacemos, cualquier otra ejecución puede modificar el valor de @@ERROR y perderíamos ese control del error sobre la consulta que deseemos.

 Ejem. Sin @variable:

CREATE TABLE notnull(a int NOT NULL)

DECLARE @value int

INSERT notnull VALUES (@value)

IF @@error <> 0

   PRINT '@@error is ' + ltrim(str(@@error)) + '.'

 La salida sería:

Server: Msg 515, Level 16, State 2, Line 3

Cannot insert the value NULL into column 'a', table

'tempdb.dbo.notnull'; column does not allow nulls. INSERT fails.

The statement has been terminated.

@@error is 0.

 Ejem. La otra forma, cargando @@ERROR en una @variable sería:

CREATE TABLE notnull(a int NOT NULL)

DECLARE @err int,

        @value int

INSERT notnull VALUES (@value)

SELECT @err = @@error

IF @err <> 0

   PRINT '@err is ' + ltrim(str(@err)) + '.'

 La salida sería:

Server: Msg 515, Level 16, State 2, Line 3

Cannot insert the value NULL into column 'a', table

'tempdb.dbo.notnull'; column does not allow nulls. INSERT fails.

The statement has been terminated.

@err is 515.

 Son de mencionar dos cosas:

 Una, que todos los procedimientos almacenados tienen un valor de retorno, determinado por la instrucción RETURN. La instrucción RETURN toma un argumento opcional, que debe ser un valor numérico. Si ponemos RETURN sin proporcionar un valor, el valor de retorno será 0 si no hay ningún error durante la ejecución. Si se produce un error durante la ejecución del procedimiento, el valor de retorno será distinto de 0. Esto ocurre aun si no ponemos RETURN ya que no es un parámetro obligado. Hay ciertos números negativos (entre -1 y -99), que puede devolver un procedimiento cuyo significado es difícil de explicar, ya que son reservados para los valores de retorno generados por el sistema. En ocasiones es interesante poder forzar que RETURN devuelva un error según la lógico de la aplicación, o incluso forzar un Warning, si nos interesa, cuando un Update o inserción no realiza ninguna modificación en la tabla. ejem:

ALTER PROCEDURE [SP_GEO_ADDR_EXT]

    @DC_STATE_CODE NVARCHAR(50),

    @DC_STREET NVARCHAR(4000),

    @DC_CITY NVARCHAR(4000),

    @DC_PHONE NVARCHAR(50),

    @DC_BRANCH_NAME NVARCHAR(4000)

AS

BEGIN

 

-- Declara las variables que usaras en los chequeos error para cada instruccion.

DECLARE @ErrorVar INT;

DECLARE @RowCountVar INT;

 

    INSERT INTO ADDRESS_EXTERNAL (type_address_type_id)

    VALUES (0)  

 

SELECT @ErrorVar = @@ERROR

    ,@RowCountVar = @@ROWCOUNT;

 

IF @ErrorVar <> 0

    BEGIN

                PRINT N'ERROR: error '

                    + RTRIM(CAST(@ErrorVar AS NVARCHAR(10)))

                    + N' occurred.';

                RETURN 1;

    END

 

-- Chequea row count. 

IF @RowCountVar  = 0

 PRINT 'Warning: Nigun registro ha sido afectado';

 RETURN 1;

GO

 

/* Resto del cuermpo del procedimiento

...*/

END

Por otro lado podemos controlar si hemos tenido error durante la ejecución de un procedimiento y lanzar el retroceso de la transacción.

   EXEC @err = some_other_sp @value OUTPUT
   SELECT @err = coalesce(nullif(@err, 0), @@error)
   IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END
 

 Para el buen entendimiento anterior, se recomienda la lectura: Stored Procedure with Input and Output Parameters. Return Values. Usar un procedimiento almacenado con parámetros de entrada y salida. SQL Server.

 Las construcciones TRY…CATCH son mucho más simples. Un bloque de instrucciones de Transact-SQL está delimitado por instrucciones BEGIN TRY y END TRY, y después se escribe un bloque CATCH para administrar los errores que pueden generarse en el bloque de instrucciones.

 Ejem. TRY…CATCH:

BEGIN TRY
   SELECT convert(smallint, '2003121')
   /* Resto de instrucciones */    
END TRY
BEGIN CATCH
   PRINT 'errno: ' + ltrim(str(error_number()))
   PRINT 'errmsg: ' + error_message()
END CATCH

 Salida:

errno: 244
errmsg: The conversion of the varchar value '2003121' overflowed
an INT2 column. Use a larger integer column.

 

Fuera de un bloque CATCH, @@ERROR es la única parte de un error de Motor de base de datos disponible en el lote, procedimiento almacenado o desencadenador que ha generado el error. El resto de las partes del error, como su gravedad, estado y texto del mensaje que contienen cadenas de sustitución (nombres de objeto, por ejemplo) sólo se devuelven a la aplicación, donde pueden procesarse mediante los mecanismos de control de errores de las API. Si el error invoca un bloque CATCH, se pueden usar las funciones del sistema ERROR_LINE, ERROR_MESSAGE, ERROR_PROCEDURE, ERROR_NUMBER, ERROR_SEVERITY y ERROR_STATE.

ERROR_NUMBER() devuelve el número del error.

ERROR_SEVERITY() devuelve la gravedad.

ERROR_STATE() devuelve el número de estado del error.

ERROR_PROCEDURE() devuelve el nombre del procedimiento almacenado o desencadenador donde se produjo el error.

ERROR_LINE() devuelve el número de línea de la rutina que provocó el error.

ERROR_MESSAGE() devuelve el texto completo del mensaje de error. Este texto incluye los valores suministrados para los parámetros reemplazables, como longitudes, nombres de objetos u horas.

 

Os dejo un ejemplo de cómo retroceder transacciones que por algún motivo producen error:

BEGIN TRANSACTION
 
   INSERT permanent_tbl1 (...)
      SELECT ...
      FROM   ...
   SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END
 
   UPDATE permanent_tbl2
   SET    ...
   FROM   #temp ....
   SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END
 
   DELETE permanent_tbl3
   WHERE  ...
   SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END
 
   EXEC @err = one_more_sp @value
   SELECT @err = coalesce(nullif(@err, 0), @@error)
   IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END
 
   COMMIT TRANSACTION

 

Es interesante tener presente las variables  @@TRANCOUNT y @@ROWCOUNT, con las que se puede jugar cuando usamos los controles de error.

@@TRANCOUNT -> Devuelve el número de instrucciones dentro de BEGIN TRANSACTION que se han producido en la conexión actual.

 Tener en cuenta que Las instrucciones en BEGIN TRANSACTION incrementa @@TRANCOUNT en 1. ROLLBACK TRANSACTION disminuye @@TRANCOUNT en 0. Cada instrucción COMMIT TRANSACTION o COMMIT WORK disminuye @@TRANCOUNT en uno.

Ejemplos:

En el ejemplo siguiente se muestra el efecto que tienen las instrucciones anidadas BEGIN y COMMIT en la variable @@TRANCOUNT:

PRINT @@TRANCOUNT

--  The BEGIN TRAN statement will increment the

--  transaction count by 1.

BEGIN TRAN

    PRINT @@TRANCOUNT

    BEGIN TRAN

        PRINT @@TRANCOUNT

--  The COMMIT statement will decrement the transaction count by 1.

    COMMIT

    PRINT @@TRANCOUNT

COMMIT

PRINT @@TRANCOUNT

--Results

--0

--1

--2

--1

--0

 

 En el ejemplo siguiente se muestra el efecto que tienen las instrucciones anidadas BEGIN TRAN y ROLLBACK en la variable @@TRANCOUNT:

 

PRINT @@TRANCOUNT

--  The BEGIN TRAN statement will increment the

--  transaction count by 1.

BEGIN TRAN

    PRINT @@TRANCOUNT

    BEGIN TRAN

        PRINT @@TRANCOUNT

--  The ROLLBACK statement will clear the @@TRANCOUNT variable

--  to 0 because all active transactions will be rolled back.

ROLLBACK

PRINT @@TRANCOUNT

--Results

--0

--1

--2

--0

 

Aquí se muestra como hacer ROLLBACK TRANSACTION, si hemos realizado inserciones en la transacción:

BEGIN TRY

    -- Generate a constraint violation error.

    DELETE FROM Production.Product

    WHERE ProductID = 980;

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 @@TRANCOUNT > 0

        ROLLBACK TRANSACTION;

END CATCH;

 

@@ROWCOUNT  -> Devuelve el número de filas afectadas por la última instrucción. Si el número de filas es mayor de 2 mil millones, use ROWCOUNT_BIG

 

UPDATE HumanResources.Employee

SET JobTitle = N'Executive'

WHERE NationalIDNumber = 123456789

IF @@ROWCOUNT = 0

PRINT 'Warning: No rows were updated';

GO

 

 

Para más información os dejo estos enlaces:

@@ERROR: http://msdn.microsoft.com/es-es/library/ms190193.aspx

TRY...CATCH: http://msdn.microsoft.com/es-es/library/ms175976.aspx

 

 

Fuentes:

Microsoft, msdn.

 

Apunte y recopilación por Norman M. Pardell

 

Puedes consultarme, si deseas cualquier aclaración, pregunta o sugerencia en: Contacto, contestaré tan pronto como me sea posible.

publicado por normanmpardell a las 05:59 · Sin comentarios  ·  Recomendar
Comentarios (0) ·  Enviar comentario
Esta entrada no admite comentarios.
img
.Sobre mí
FOTO

Norman M. Pardell

MCITP: Database Administrator & Database Developer, SQL Server 2008. MCC Award Certificate. Consultor Senior de bases de datos en Capgemini España, S.L. Asesoramiento en implementación, desarrollo y gestión de bases de datos en grandes compañías. Actualmente, asignado a proyecto en compañía líder en el sector energético global. Más de 10 años trabajando con SQL Server (y otros gestores de BBDD)

» Ver perfil

img
.Secciones
» Inicio
img
.Enlaces
» Microsoft MSDN Foros
» Windows Server 2012
img
.Más leídos
» Asignar la cantidad correcta de Memoria para SQL Server
» Base de Datos Sospechosa (Suspect)
» Como modificar la Intercalación (Collation) en SQL Server
» Detectar bloqueos. SQL Server V.2005 y superiores
» Funciones SQL Server. Funciones escalares y funciones con valores de tabla.
» Integridad y corrupción en las bases de datos: DBCC CHECKDB
» Log de transacciones ( .ldf ). SQL Server.
» Migrando SQL Server 2005, 2008, 2008 R2 a SQL Server 2012
» Tomo I. Memoria RAM. Optimización de sistemas de 32 y 64 bits. SQL Server 2008.
» Transacciones activas. SQL server 2008
img
.Nube de tags [?]
                                                           
img img
FULLServices Network | Crear blog | Privacidad