Utilisation de TRY...CATCH dans Transact-SQL
Les erreurs du code Transact-SQL peuvent être traitées à l'aide d'une construction TRY…CATCH semblable aux fonctionnalités de gestion des exceptions des langages Microsoft Visual C++ et Microsoft Visual C# . Une construction TRY…CATCH comprend deux parties : un bloc TRY et un bloc CATCH. Lorsque le système détecte une condition d'erreur dans une instruction Transact-SQL incluse dans un bloc TRY, le contrôle est transmis à un bloc CATCH où l'erreur peut être traitée.
Une fois que le bloc CATCH a traité l'exception, le contrôle est transféré à la première instruction Transact-SQL qui suit l'instruction END CATCH. Si l'instruction END CATCH représente la dernière instruction d'une procédure stockée ou d'un déclencheur, le contrôle est retourné au code qui a appelé la procédure stockée ou le déclencheur. Les instructions Transact-SQL du bloc TRY suivant l'instruction qui génère une erreur ne sont pas exécutées.
En l'absence d'erreurs dans le bloc TRY, le contrôle est transmis à l'instruction située immédiatement après l'instruction END CATCH associée. Si l'instruction END CATCH est la dernière instruction d'une procédure stockée ou d'un déclencheur, le contrôle est transmis à l'instruction qui a appelé la procédure stockée ou le déclencheur.
Un bloc TRY commence par l'instruction BEGIN TRY et finit par l'instruction END TRY. Vous pouvez spécifier une ou plusieurs instructions Transact-SQL entre les instructions BEGIN TRY et END TRY.
Un bloc TRY doit être immédiatement suivi d'un bloc CATCH. Un bloc CATCH commence par l'instruction BEGIN CATCH et finit par l'instruction END CATCH. Dans Transact-SQL, chaque bloc TRY est associé à un seul bloc CATCH.
Utilisation de TRY…CATCH
Lorsque vous utilisez la construction TRY…CATCH, tenez compte des directives et des suggestions suivantes :
Chaque construction TRY…CATCH doit être contenue dans un traitement, une procédure stockée ou un déclencheur unique. Par exemple, vous ne pouvez pas insérer un bloc TRY dans un traitement et le bloc CATCH associé dans un autre traitement. Le script suivant génèrerait une erreur :
BEGIN TRY SELECT * FROM sys.messages WHERE message_id = 21; END TRY GO -- The previous GO breaks the script into two batches, -- generating syntax errors. The script runs if this GO -- is removed. BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber; END CATCH; GOUn bloc TRY doit être immédiatement suivi d'un bloc CATCH.
Les constructions TRY…CATCH peuvent être imbriquées. Cela signifie que les constructions TRY…CATCH peuvent être insérées dans d'autres blocs TRY et CATCH. Lorsqu'une erreur se produit dans un bloc TRY imbriqué, le contrôle du programme est transféré au bloc CATCH associé au bloc TRY imbriqué.
Pour gérer une erreur qui se produit dans un bloc CATCH donné, écrivez un bloc TRY…...CATCH dans le bloc CATCH spécifié.
Les erreurs dont le niveau de gravité est supérieur ou égal à 20 et qui contraignent le moteur de base de données à fermer la connexion ne sont pas gérées par le bloc TRY…CATCH. Toutefois, le bloc TRY…CATCH gère les erreurs dont le niveau de gravité est supérieur ou égal à 20 tant que la connexion n'est pas fermée.
Les erreurs dont le niveau de gravité est inférieur ou égal à 10 sont considérées comme des avertissements ou des messages d'information et ne sont pas gérées par les blocs TRY…CATCH.
Les avertissements mettent fin à un traitement même si celui-ci se trouve dans l'étendue d'une construction TRY…CATCH. Cela concerne, entre autres, l'avertissement envoyé par MS DTC (Microsoft Distributed Transaction Coordinator) lorsqu'une transaction distribuée échoue. MS DTC gère les transactions distribuées.
Notes
Si une transaction distribuée s'exécute dans l'étendue d'un bloc TRY et qu'une erreur se produit, l'exécution est transférée au bloc CATCH associé. La transaction distribuée adopte un état non validable. L'exécution dans le bloc CATCH peut être interrompue par le service Microsoft Distributed Transaction Coordinator qui gère les transactions distribuées. Lorsque l'erreur se produit, MS DTC notifie de façon asynchrone tous les serveurs qui participent à la transaction distribuée et met fin à toutes les tâches impliquées dans celle-ci. Le système transmet cette notification sous la forme d'un avertissement qui n'est pas géré par une construction TRY…CATCH, puis met fin au traitement. Une fois l'exécution d'un traitement terminée, le moteur de base de données restaure toutes les transactions non validables actives. Si aucun message d'erreur n'a été transmis au moment où la transaction a adopté un état non validable, une fois le traitement exécuté, un message d'erreur envoyé à l'application cliente indique qu'une transaction non validable a été détectée et restaurée. Pour plus d'informations sur les transactions distribuées, consultez Transactions distribuées (moteur de base de données).
Fonctions d'erreur
Le bloc TRY…CATCH utilise les fonctions de gestion des erreurs suivantes pour capturer les informations d'erreur :
ERROR_NUMBER() retourne le numéro de l'erreur.
ERROR_MESSAGE() retourne le texte complet du message d'erreur. Le texte comprend les valeurs fournies pour tous les paramètres substituables, tels que les longueurs, les noms d'objets ou les heures.
ERROR_SEVERITY() retourne le niveau de gravité.
ERROR_STATE() retourne le numéro d'état de l'erreur.
ERROR_LINE() retourne le numéro de ligne de la routine à l'origine de l'erreur.
ERROR_PROCEDURE() retourne le nom de la procédure stockée ou du déclencheur dans lequel l'erreur s'est produite.
Ces fonctions permettent de récupérer les informations d'erreur de n'importe où dans l'étendue du bloc CATCH d'une construction TRY…CATCH. Elles retournent la valeur NULL si elles sont appelées en dehors de l'étendue d'un bloc CATCH. Elles peuvent être référencées dans une procédure stockée et utilisées pour récupérer les informations d'erreur lorsque la procédure stockée est exécutée dans le bloc CATCH. De cette manière, vous n'avez pas besoin de répéter le code de gestion d'erreur dans chaque bloc CATCH. Dans l'exemple de code ci-dessous, l'instruction SELECT du bloc TRY génère une erreur de division par zéro. L'erreur est gérée par le bloc CATCH qui utilise une procédure stockée pour retourner les informations d'erreur.
USE AdventureWorks2008R2;
GO
-- Verify that the stored procedure does not exist.
IF OBJECT_ID ('usp_GetErrorInfo', 'P') IS NOT NULL
DROP PROCEDURE usp_GetErrorInfo;
GO
-- Create a procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
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;
GO
BEGIN TRY
-- Generate divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
-- Execute the error retrieval routine.
EXECUTE usp_GetErrorInfo;
END CATCH;
GO
Erreurs de compilation et de recompilation de niveau instruction
Il existe deux types d'erreurs qui ne sont pas gérés par TRY…CATCH si l'erreur se produit au même niveau d'exécution que la construction TRY…CATCH :
Les erreurs de compilation, telles que les erreurs de syntaxe qui empêchent l'exécution d'un traitement.
Les erreurs qui se produisent pendant une recompilation de niveau instruction, telles que les erreurs de résolution de nom d'objet qui surviennent après la compilation en raison d'une résolution de nom différée.
Lorsque le traitement, la procédure stockée ou le déclencheur qui contient la construction TRY…CATCH génère l'une de ces erreurs, la construction TRY…CATCH ne gère pas celle-ci. Ces erreurs sont retournées à l'application ou au traitement qui a appelé la routine de génération d'erreur. Par exemple, l'exemple de code suivant illustre une instruction SELECT qui entraîne une erreur de syntaxe. Si ce code est exécuté dans l'Éditeur de requête de SQL Server Management Studio, l'exécution ne démarre pas en raison de l'échec de compilation du traitement. L'erreur est retournée par l'Éditeur de requête et n'est pas interceptée par TRY…CATCH.
USE AdventureWorks2008R2;
GO
BEGIN TRY
-- This PRINT statement will not run because the batch
-- does not begin execution.
PRINT N'Starting execution';
-- This SELECT statement contains a syntax error that
-- stops the batch from compiling successfully.
SELECT ** FROM HumanResources.Employee;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
Contrairement à l'erreur de syntaxe illustrée dans l'exemple précédent, une erreur qui se produit lors de la recompilation des instructions n'empêche pas la compilation du traitement, mais elle met fin à celui-ci dès que la recompilation de l'instruction échoue. Par exemple, si un traitement possède deux instructions et que la deuxième instruction fait référence à une table inexistante, la résolution de nom différée permet la compilation adéquate du traitement et le démarrage de son exécution sans que la table manquante soit liée au plan de requête avant la recompilation de cette instruction. L'exécution du traitement prend fin lorsque celui-ci atteint l'instruction qui fait référence à la table manquante et retourne une erreur. Ce type d'erreur n'est pas géré par une construction TRY…CATCH au même niveau d'exécution que celui auquel l'erreur s'est produite. L'exemple ci-dessous illustre ce comportement.
USE AdventureWorks2008R2;
GO
BEGIN TRY
-- This PRINT statement will run because the error
-- occurs at the SELECT statement.
PRINT N'Starting execution';
-- This SELECT statement will generate an object name
-- resolution error because the table does not exist.
SELECT * FROM NonExistentTable;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
Pour utiliser le bloc TRY…CATCH afin de gérer les erreurs survenant lors de la compilation ou la recompilation des instructions, vous pouvez exécuter le code de génération d'erreur dans un traitement distinct au sein du bloc TRY. Pour ce faire, vous pouvez, par exemple, placer le code dans une procédure stockée ou exécuter une instruction Transact-SQL dynamique à l'aide de sp_executesql. Cela permet à TRY…CATCH d'intercepter l'erreur à un niveau d'exécution supérieur à celui auquel elle se produit. L'exemple de code ci-dessous montre une procédure stockée qui génère une erreur de résolution de nom d'objet. Le lot contenant la construction TRY…CATCH s'exécute à un niveau supérieur à la procédure stockée ; l'erreur qui survient à un niveau inférieur est interceptée.
USE AdventureWorks2008R2;
GO
-- Verify that the stored procedure does not already exist.
IF OBJECT_ID ('usp_MyError', 'P') IS NOT NULL
DROP PROCEDURE usp_MyError;
GO
CREATE PROCEDURE usp_MyError
AS
-- This SELECT statement will generate
-- an object name resolution error.
SELECT * FROM NonExistentTable;
GO
BEGIN TRY
-- Run the stored procedure.
EXECUTE usp_MyError;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
Voici l'ensemble des résultats.
ErrorNumber ErrorMessage
----------- ---------------------------------------
208 Invalid object name 'NonExistentTable'.
Pour plus d'informations, consultez Résolution de noms différée et compilation et la section « Recompilation des plans d'exécution » dans Mise en mémoire cache et réutilisation du plan d'exécution.
Transactions non validables
Au sein d'une construction TRY…CATCH, les transactions peuvent passer dans un état dans lequel elles demeurent ouvertes mais qui ne permet pas leur validation. La transaction ne peut réaliser aucune action susceptible de générer une écriture dans le journal des transactions, notamment la modification de données ou une tentative de restauration jusqu'à un point d'enregistrement. Toutefois, dans cet état, les verrous acquis par la transaction sont conservés et la connexion demeure ouverte. Les effets de la transaction ne sont pas inversés tant qu'une instruction ROLLBACK n'est pas émise ou tant que le traitement n'est pas exécuté et que la transaction est automatiquement restaurée par le moteur de base de données. Si aucun message d'erreur n'a été transmis au moment où la transaction a adopté un état non validable, une fois le traitement exécuté, un message d'erreur envoyé à l'application cliente indique qu'une transaction non validable a été détectée et restaurée.
Une transaction adopte un état non validable au sein d'un bloc TRY lorsqu'une erreur se produit qui, dans d'autres conditions, mettrait fin à la transaction. Par exemple, la plupart des erreurs d'une instruction DDL (Data Definition Language), telle que CREATE TABLE, ou des erreurs qui se produisent lorsque SET XACT_ABORT a la valeur ON mettent fin à la transaction en dehors d'un bloc TRY mais rendent une transaction non validable à l'intérieur d'un bloc TRY.
Le code d'un bloc CATCH doit tester l'état d'une transaction à l'aide de la fonction XACT_STATE. XACT_STATE retourne -1 si la session contient une transaction qui ne peut pas être validée. Le bloc CATCH ne doit exécuter aucune action susceptible de générer des écritures dans le journal si XACT_STATE retourne une valeur -1. L'exemple de code ci-dessous génère une erreur à partir d'une instruction DDL et utilise XACT_STATE pour tester l'état d'une transaction afin d'exécuter l'action la plus appropriée.
USE AdventureWorks2008R2;
GO
-- Verify that the table does not exist.
IF OBJECT_ID (N'my_books', N'U') IS NOT NULL
DROP TABLE my_books;
GO
-- Create table my_books.
CREATE TABLE my_books
(
Isbn int PRIMARY KEY,
Title NVARCHAR(100)
);
GO
BEGIN TRY
BEGIN TRANSACTION;
-- This statement will generate an error because the
-- column author does not exist in the table.
ALTER TABLE my_books
DROP COLUMN author;
-- If the DDL statement succeeds, commit the transaction.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage;
-- Test XACT_STATE for 1 or -1.
-- XACT_STATE = 0 means there is no transaction and
-- a commit or rollback operation would generate an error.
-- Test whether the transaction is uncommittable.
IF (XACT_STATE()) = -1
BEGIN
PRINT
N'The transaction is in an uncommittable state. ' +
'Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
-- Test whether the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
PRINT
N'The transaction is committable. ' +
'Committing transaction.'
COMMIT TRANSACTION;
END;
END CATCH;
GO
Gestion des blocages
TRY…CATCH peut être utilisé pour gérer les blocages. L'erreur de victime de blocage 1205 peut être interceptée par le bloc CATCH et la transaction peut être restaurée jusqu'à ce que les threads soient déverrouillées. Pour plus d'informations sur le blocage, consultez Interblocage.
L'exemple suivant montre comment TRY…CATCH peut être utilisé pour gérer les blocages. Cette première section crée une table qui permet d'illustrer un état de blocage, ainsi qu'une procédure stockée qui permet d'imprimer les informations d'erreur.
USE AdventureWorks2008R2;
GO
-- Verify that the table does not exist.
IF OBJECT_ID (N'my_sales',N'U') IS NOT NULL
DROP TABLE my_sales;
GO
-- Create and populate the table for deadlock simulation.
CREATE TABLE my_sales
(
Itemid INT PRIMARY KEY,
Sales INT not null
);
GO
INSERT my_sales (itemid, sales) VALUES (1, 1);
INSERT my_sales (itemid, sales) VALUES (2, 1);
GO
-- Verify that the stored procedure for error printing
-- does not exist.
IF OBJECT_ID (N'usp_MyErrorLog',N'P') IS NOT NULL
DROP PROCEDURE usp_MyErrorLog;
GO
-- Create a stored procedure for printing error information.
CREATE PROCEDURE usp_MyErrorLog
AS
PRINT
'Error ' + CONVERT(VARCHAR(50), ERROR_NUMBER()) +
', Severity ' + CONVERT(VARCHAR(5), ERROR_SEVERITY()) +
', State ' + CONVERT(VARCHAR(5), ERROR_STATE()) +
', Line ' + CONVERT(VARCHAR(5), ERROR_LINE());
PRINT
ERROR_MESSAGE();
GO
Le code suivant génère le script des sessions 1 et 2, exécutées simultanément dans deux connexions SQL Server Management Studio distinctes. Les deux sessions essaient de mettre à jour les mêmes lignes de la table. L'une des sessions parvient à effectuer la mise à jour dès la première tentative alors que l'autre est choisie comme victime du blocage. L'erreur de victime de blocage aiguille l'exécution vers le bloc CATCH et la transaction passe dans un état non validable. Dans le bloc CATCH, la victime du blocage peut restaurer la transaction et renouveler la tentative de mise à jour de la table jusqu'à ce que l'actualisation réussisse ou que le nombre limite de tentatives soit atteint.
Session 1 |
Session 2 |
|---|---|
|
|
TRY…CATCH avec RAISERROR
Vous pouvez utiliser RAISERROR dans le bloc TRY ou CATCH d'une construction TRY…CATCH pour définir la façon dont les erreurs sont gérées.
Si RAISERROR a un niveau de gravité compris entre 11 et 19 et qu'il est exécuté dans un bloc TRY, le contrôle est transféré au bloc CATCH associé. Si RAISERROR possède un niveau de gravité compris entre 11 et 19 et qu'il est exécuté dans un bloc CATCH, il retourne une erreur à l'application ou au traitement appelant. Ainsi, RAISERROR permet de retourner à l'appelant des informations sur l'erreur à l'origine de l'exécution du bloc CATCH. Les informations d'erreur fournies par les fonctions d'erreur TRY…CATCH peuvent être capturées dans le message RAISERROR, notamment le numéro d'erreur d'origine ; toutefois, le numéro d'erreur de RAISERROR doit être supérieur ou égal à 50000.
Si RAISERROR possède un niveau de gravité inférieur ou égal à 10, il retourne un message d'information à l'application ou au traitement appelant sans solliciter un bloc CATCH.
Si RAISERROR possède un niveau de gravité supérieur ou égal à 20, il met fin à la connexion à la base de données sans appeler le bloc CATCH.
L'exemple de code ci-dessous illustre l'utilisation de RAISERROR dans un bloc CATCH pour retourner les informations d'erreur d'origine à l'application ou au traitement appelant. La procédure stockée usp_GenerateError exécute une instruction DELETE dans un bloc TRY qui génère une erreur de violation de contrainte. L'erreur provoque le transfert vers le bloc CATCH associé dans usp_GenerateError, où la procédure stockée usp_RethrowError est exécutée pour générer les informations d'erreur de violation de contrainte à l'aide de RAISERROR. Cette erreur générée par RAISERROR est retournée au traitement appelant où usp_GenerateError a été exécutée et provoque le transfert vers le bloc CATCH associé dans le traitement appelant.
Notes
RAISERROR peut générer des erreurs dont l'état est exclusivement compris entre 1 et 27. Le moteur de base de données étant susceptible de déclencher des erreurs dont l'état est égal à 0, nous vous recommandons de vérifier l'état d'erreur retourné par ERROR_STATE avant de le transmettre comme valeur au paramètre d'état de RAISERROR.
USE AdventureWorks2008R2;
GO
-- Verify that stored procedure does not exist.
IF OBJECT_ID (N'usp_RethrowError',N'P') IS NOT NULL
DROP PROCEDURE usp_RethrowError;
GO
-- Create the stored procedure to generate an error using
-- RAISERROR. The original error information is used to
-- construct the msg_str for RAISERROR.
CREATE PROCEDURE usp_RethrowError AS
-- Return if there is no error information to retrieve.
IF ERROR_NUMBER() IS NULL
RETURN;
DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200);
-- Assign variables to error-handling functions that
-- capture information for RAISERROR.
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
-- Build the message string that will contain original
-- error information.
SELECT @ErrorMessage =
N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
'Message: '+ ERROR_MESSAGE();
-- Raise an error: msg_str parameter of RAISERROR will contain
-- the original error information.
RAISERROR
(
@ErrorMessage,
@ErrorSeverity,
1,
@ErrorNumber, -- parameter: original error number.
@ErrorSeverity, -- parameter: original error severity.
@ErrorState, -- parameter: original error state.
@ErrorProcedure, -- parameter: original error procedure name.
@ErrorLine -- parameter: original error line number.
);
GO
-- Verify that stored procedure does not exist.
IF OBJECT_ID (N'usp_GenerateError',N'P') IS NOT NULL
DROP PROCEDURE usp_GenerateError;
GO
-- Create a stored procedure that generates a constraint violation
-- error. The error is caught by the CATCH block where it is
-- raised again by executing usp_RethrowError.
CREATE PROCEDURE usp_GenerateError
AS
BEGIN TRY
-- A FOREIGN KEY constraint exists on the table. This
-- statement will generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
END TRY
BEGIN CATCH
-- Call the procedure to raise the original error.
EXEC usp_RethrowError;
END CATCH;
GO
-- In the following batch, an error occurs inside
-- usp_GenerateError that invokes the CATCH block in
-- usp_GenerateError. RAISERROR inside this CATCH block
-- generates an error that invokes the outer CATCH
-- block in the calling batch.
BEGIN TRY -- outer TRY
-- Call the procedure to generate an error.
EXECUTE usp_GenerateError;
END TRY
BEGIN CATCH -- Outer CATCH
SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage;
END CATCH;
GO
Modification du flux d'exécution
Pour modifier le flux d'exécution, vous pouvez utiliser GOTO dans un bloc TRY ou CATCH. Vous pouvez également utiliser GOTO pour quitter un bloc TRY ou CATCH ; toutefois, vous ne pouvez pas y recourir pour entrer dans un bloc TRY ou CATCH.
Solution de gestion d'erreur dans l'exemple de base de données AdventureWorks2008R2
La base de données exemple AdventureWorks2008R2 comprend une solution de gestion d'erreur qui permet de consigner les informations relatives aux erreurs interceptées par le bloc CATCH d'une construction TRY…CATCH en vue d'une interrogation ou d'une analyse ultérieure.
Table dbo.ErrorLog
La table ErrorLog enregistre des informations sur le numéro de l'erreur, son niveau de gravité, son état, le nom de la procédure stockée ou du déclencheur où elle s'est produite, le numéro de ligne auquel elle s'est produite et le texte complet du message d'erreur. Elle enregistre également la date et l'heure auxquelles l'erreur s'est produite et le nom de l'utilisateur qui a exécuté la routine de génération d'erreur. Cette table est remplie lorsque la procédure stockée uspLogError est exécutée dans l'étendue du bloc CATCH d'une construction TRY…CATCH.
dbo.uspLogError
La procédure stockée uspLogError consigne, dans la table ErrorLog, les informations relatives à l'erreur qui a provoqué le transfert de l'exécution vers le bloc CATCH d'une construction TRY…CATCH. Pour que la procédure stockée uspLogError insère les informations d'erreur dans la table ErrorLog, les conditions suivantes doivent être réunies :
La procédure stockée uspLogError est exécutée dans l'étendue d'un bloc CATCH.
Si la transaction en cours se trouve dans un état non validable, elle est restaurée avant l'exécution de uspLogError.
Le paramètre de sortie @ErrorLogID de uspLogError retourne la valeur ErrorLogID de la ligne insérée par uspLogError dans la table ErrorLog. La valeur par défaut de @ErrorLogID est 0. L'exemple suivant montre le code de la procédure stockée uspLogError.
CREATE PROCEDURE [dbo].[uspLogError]
@ErrorLogID [int] = 0 OUTPUT -- Contains the ErrorLogID of the row inserted
-- by uspLogError in the ErrorLog table.
AS
BEGIN
SET NOCOUNT ON;
-- Output parameter value of 0 indicates that error
-- information was not logged.
SET @ErrorLogID = 0;
BEGIN TRY
-- Return if there is no error information to log.
IF ERROR_NUMBER() IS NULL
RETURN;
-- Return if inside an uncommittable transaction.
-- Data insertion/modification is not allowed when
-- a transaction is in an uncommittable state.
IF XACT_STATE() = -1
BEGIN
PRINT 'Cannot log error since the current transaction is in an uncommittable state. '
+ 'Rollback the transaction before executing uspLogError in order to successfully log error information.';
RETURN;
END;
INSERT [dbo].[ErrorLog]
(
[UserName],
[ErrorNumber],
[ErrorSeverity],
[ErrorState],
[ErrorProcedure],
[ErrorLine],
[ErrorMessage]
)
VALUES
(
CONVERT(sysname, CURRENT_USER),
ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_PROCEDURE(),
ERROR_LINE(),
ERROR_MESSAGE()
);
-- Pass back the ErrorLogID of the row inserted
SELECT @ErrorLogID = @@IDENTITY;
END TRY
BEGIN CATCH
PRINT 'An error occurred in stored procedure uspLogError: ';
EXECUTE [dbo].[uspPrintError];
RETURN -1;
END CATCH
END;
dbo.uspPrintError
La procédure stockée uspPrintError imprime les informations relatives à l'erreur qui a provoqué le transfert de l'exécution vers le bloc CATCH d'une construction TRY…CATCH. La procédure stockée uspPrintErrordoit être exécutée dans l'étendue d'un bloc CATCH, sinon aucune information d'erreur n'est imprimée. L'exemple suivant montre le code de la procédure stockée uspPrintError.
CREATE PROCEDURE [dbo].[uspPrintError]
AS
BEGIN
SET NOCOUNT ON;
-- Print error information.
PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) +
', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
', State ' + CONVERT(varchar(5), ERROR_STATE()) +
', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') +
', Line ' + CONVERT(varchar(5), ERROR_LINE());
PRINT ERROR_MESSAGE();
END;
Exemple de gestion d'erreur
L'exemple suivant illustre la solution de gestion d'erreur de AdventureWorks2008R2. Le code inscrit dans le bloc TRY tente de supprimer l'enregistrement avec ProductID 980 dans la table Production.Product. Une contrainte FOREIGN KEY appliquée à la table empêche l'exécution en bonne et due forme de l'instruction DELETE et une erreur de violation de contrainte est générée. Cette erreur provoque le transfert de l'exécution vers le bloc CATCH. Les actions suivantes se produisent dans le bloc CATCH :
uspPrintError imprime les informations d'erreur.
Une fois la transaction restaurée, uspLogError insère les informations d'erreur dans la table ErrorLog et retourne l'ErrorLogID de la ligne insérée dans le paramètre @ErrorLogID OUTPUT.
USE AdventureWorks2008R2;
GO
-- Variable to store ErrorLogID value of the row
-- inserted in the ErrorLog table by uspLogError
DECLARE @ErrorLogID INT;
BEGIN TRY
BEGIN TRANSACTION;
-- A FOREIGN KEY constraint exists on this table. This
-- statement will generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
-- If the delete operation succeeds, commit the transaction.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Call procedure to print error information.
EXECUTE dbo.uspPrintError;
-- Roll back any active or uncommittable transactions before
-- inserting information in the ErrorLog.
IF XACT_STATE() <> 0
BEGIN
ROLLBACK TRANSACTION;
END
EXECUTE dbo.uspLogError @ErrorLogID = @ErrorLogID OUTPUT;
END CATCH;
-- Retrieve logged error information.
SELECT * FROM dbo.ErrorLog WHERE ErrorLogID = @ErrorLogID;
GO
Exemple de gestion d'erreur imbriquée
L'exemple suivant montre l'utilisation des constructions imbriquées TRY…CATCH.
BEGIN TRY
BEGIN TRY
SELECT CAST('invalid_date' AS datetime)
END TRY
BEGIN CATCH
PRINT 'Inner TRY error number: ' +
CONVERT(varchar,ERROR_NUMBER()) + ' on line: ' +
CONVERT(varchar, ERROR_LINE())
END CATCH
SELECT CAST('invalid_int' AS int)
END TRY
BEGIN CATCH
PRINT 'Outer TRY error mumber: ' + CONVERT(varchar,ERROR_NUMBER())+
' on line: ' + CONVERT(varchar, ERROR_LINE())
END CATCH
Voici l'ensemble des résultats.
Inner TRY error number: 241 on line: 3
Outer TRY error number: 245 on line: 9
Voir aussi