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
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.
