Mise à jour de définition de code : risques du DROP / CREATE

Publié le par

Pour mettre à jour une procédure stockée, une vue ou encore d’autres objets SQL Server disposant d’une définition sous forme T-SQL, une solution classique consiste à supprimer la définition actuelle de l’objet (DROP) et à recréer ensuite l’objet avec sa définition mise à jour. C’est d’ailleurs ce type de code que construit SQL Server Management Studio (SSMS).

Mais il est en fait parfois plus judicieux d’utiliser une méthode implémentant une mise à jour (commande ALTER), qui peut présenter des avantages non négligeables.

Pour ce petit tour du propriétaire, nous nous appuierons sur l’exemple d’une procédure stockée que nous cherchons à mettre à jour, mais les remarques seraient aussi valable pour un déclencheur (TRIGGER), une vue (VIEW) ou encore d’autre types de modules de code.

Supposons donc que nous avons une procédure stockée existante que nous cherchons à mettre à jour. Par exemple, la procédure dbo.uspLogError de la base de données AdventureWorks 2012 (voir ici pour télécharger les bases exemples de SQL Server 2012). Dans le contexte d’une modification en environnement de développement destinée à être propagée en environnement de recette puis de production, nous devons générer un script de création de la procédure.

Utilisons donc les outils intégrés à SQL Server Management Studio.

On obtient un script qui dans un premier temps supprimer la procédure stockée si elle existe, et ensuite la crée d’après le code source correspondant.

USE [AdventureWorks2012]
GO

IF  EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'PROCEDURE',N'uspLogError', N'PARAMETER',N'@ErrorLogID'))
EXEC sys.sp_dropextendedproperty @name=N'MS_Description' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'uspLogError', @level2type=N'PARAMETER',@level2name=N'@ErrorLogID'

GO

IF  EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'PROCEDURE',N'uspLogError', NULL,NULL))
EXEC sys.sp_dropextendedproperty @name=N'MS_Description' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'uspLogError'

GO

/****** Object:  StoredProcedure [dbo].[uspLogError]    Script Date: 29/07/2012 17:19:55 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspLogError]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspLogError]
GO

/****** Object:  StoredProcedure [dbo].[uspLogError]    Script Date: 29/07/2012 17:19:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspLogError]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'

-- uspLogError logs error information in the ErrorLog table about the 
-- error that caused execution to jump to the CATCH block of a 
-- TRY...CATCH construct. This should be executed from within the scope 
-- of a CATCH block otherwise it will return without inserting error 
-- information. 
CREATE PROCEDURE [dbo].[uspLogError] 
    @ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted
AS                               -- by uspLogError in the ErrorLog table
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
        SET @ErrorLogID = @@IDENTITY;
    END TRY
    BEGIN CATCH
        PRINT ''An error occurred in stored procedure uspLogError: '';
        EXECUTE [dbo].[uspPrintError];
        RETURN -1;
    END CATCH
END;

' 
END
GO

IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'PROCEDURE',N'uspLogError', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Logs error information in the ErrorLog table about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without inserting error information.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'uspLogError'
GO

IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'PROCEDURE',N'uspLogError', N'PARAMETER',N'@ErrorLogID'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Output parameter for the stored procedure uspLogError. Contains the ErrorLogID value corresponding to the row inserted by uspLogError in the ErrorLog table.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'uspLogError', @level2type=N'PARAMETER',@level2name=N'@ErrorLogID'
GO

A noter toutefois un petit détail de configuration : pour être sûr d’avoir un test d’existence, notamment avant l’instruction DROP, il convient de bien configurer les options du générateur de scripts en ce sens.

On obtient donc un script qui ira très bien pour un déploiement standard.

Néanmoins, considérons le cas où la base cible aurait quelques attributions de droits spécifiques pour cette procédure stockée. Par exemple :

Dans ce cas, lors de l’exécution du script précédent (qui est quoi qu’il en soit généré de la même façon), ces droits spécifiques disparaissent.

En effet, la suppression de la procédure stockée efface de facto ces droits spécifiques, et donc la simple création de la procédure « nouvelle version » n’est pas suffisante pour les rétablir.

En fait, il convient d’utiliser la commande ALTER pour modifier la procédure stockée. De fait, il n’y aura pas de modification des différences habilitations spécifiques.

-- uspLogError logs error information in the ErrorLog table about the 
-- error that caused execution to jump to the CATCH block of a 
-- TRY...CATCH construct. This should be executed from within the scope 
-- of a CATCH block otherwise it will return without inserting error 
-- information. 
ALTER PROCEDURE [dbo].[uspLogError] 
    @ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted
AS                               -- by uspLogError in the ErrorLog table
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
        SET @ErrorLogID = @@IDENTITY;
    END TRY
    BEGIN CATCH
        PRINT ''An error occurred in stored procedure uspLogError: '';
        EXECUTE [dbo].[uspPrintError];
        RETURN -1;
    END CATCH
END;

Mais alors, comment faire pour que ce code soit passe-partout, c’est à dire qu’il crée la procédure si celle-ci n’existe pas ?

Et bien tout simplement en créant une coquille vide si besoin, en positionnant le code ci-dessous avant le ALTER :

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspLogError]') AND type in (N'P', N'PC'))
exec sp_executesql N'CREATE PROCEDURE [dbo].[uspLogError] AS RETURN;'
GO

Au final, chacun choisira donc son mode de déploiement, mais j’ai personnellement une certaine préférence pour le couple CREATE/ALTER, par opposition au couple standard DROP/CREATE…

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Etes-vous un robot ? *Chargement du capcha...

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.