Gestion d’erreur dans un procédure stockée TSQL

Cet article décrit une méthode pour gérer les erreurs dans une procédure stockée et comment annuler la transaction.

Il faut tout d’abord avoir une table pour logger les erreurs.

Création de la table

CREATE TABLE [dbo].[tblErrorSql](
	[ErrorSqlID] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [varchar](100) NULL,
	[ErrorNumber] [int] NULL,
	[ErrorState] [int] NULL,
	[ErrorSeverity] [int] NULL,
	[ErrorLine] [int] NULL,
	[ErrorProcedure] [varchar](max) NULL,
	[ErrorMessage] [varchar](max) NULL,
	[ErrorDateTime] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_tblErrorSql] PRIMARY KEY CLUSTERED 
(
	[ErrorSqlID] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblErrorSql] ADD  CONSTRAINT [DF_tblErrorSql_CreationDate]  DEFAULT (getdate()) FOR [ErrorDateTime]
GO

Voici un template d’une procédure stockée avec la gestion d’erreur et de transaction :

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--=====================================================================================================
--	PROCEDURE:			sp__Template
--
--	DESCRIPTION:		Faire XYZ
--
--	AFFECTED TABLE(S):	dbo.tblErrorSql
--
--	USED BY: 			XYZ
--=====================================================================================================
--	SUMMARY OF CHANGES
--
-- 	DATE(yyyy-mm-dd)	AUTHOR						COMMENTS
--
--	2024-06-28			XYZ							- Creation
--
--  EXEC dbo.sp__Template
--=====================================================================================================
ALTER PROCEDURE [dbo].[sp__Template]

AS
BEGIN TRANSACTION;
BEGIN TRY

	SET NOCOUNT ON;

	-- XYZ

END TRY

BEGIN CATCH

	IF @@TRANCOUNT > 0  
        ROLLBACK TRANSACTION;

	INSERT INTO dbo.tblErrorSql (UserName, ErrorNumber, ErrorState, ErrorSeverity,  ErrorProcedure, ErrorLine, ErrorMessage)
    SELECT SUSER_SNAME(), ERROR_NUMBER(), ERROR_STATE(), ERROR_SEVERITY(),  ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE();

	SELECT 0 AS Result, ERROR_MESSAGE() AS Msg;

END CATCH;

IF @@TRANCOUNT > 0 BEGIN
    COMMIT TRANSACTION; 
	SELECT 1 AS Result, 'OK' AS Msg;
END
GO

La fonctionnalité que vous voulez programmer s’exécute à l’intérieur du bloc BEGIN TRY … END TRY.

Si une erreur est lancée, le bloc BEGIN CATCH … END CATCH s’exécute.

Dans le bloc CATCH, nous allons sauvegarder le message d’erreur dans la table tblSqlError et faire un Rollback sur la transaction. Notez que le rollback doit se faire avant le log d’erreur sinon le log d’erreur sera rollbacker.

S’il n’y a pas eu d’erreur lors de l’exécution de la procédure stockée, nous pouvons faire un Commit à la fin du script.

Si vous voulez Commiter une partie de l’exécution de votre procédure stockée et Rollbacker où il y a des erreurs, il y a une fonction scalaire qui vous permet de le faire. Plus d’information sur : https://learn.microsoft.com/en-us/sql/t-sql/functions/xact-state-transact-sql?view=sql-server-ver16