{"id":411,"date":"2012-08-01T00:00:30","date_gmt":"2012-07-31T22:00:30","guid":{"rendered":"http:\/\/www.sqlserver.fr\/blog\/?p=411"},"modified":"2026-05-02T14:31:04","modified_gmt":"2026-05-02T12:31:04","slug":"mise-a-jour-de-definition-de-code-risques-du-drop-create","status":"publish","type":"post","link":"https:\/\/www.sqlserver.fr\/blog\/mise-a-jour-de-definition-de-code-risques-du-drop-create\/","title":{"rendered":"Mise \u00e0 jour de d\u00e9finition de code : risques du DROP \/ CREATE"},"content":{"rendered":"

Pour mettre \u00e0 jour une proc\u00e9dure stock\u00e9e, une vue ou encore d’autres objets SQL Server disposant d’une d\u00e9finition sous forme T-SQL, une solution classique consiste \u00e0 supprimer la d\u00e9finition actuelle de l’objet (DROP) et \u00e0 recr\u00e9er ensuite l’objet avec sa d\u00e9finition mise \u00e0 jour. C’est d’ailleurs ce type de code que construit SQL Server Management Studio (SSMS).<\/p>\n

Mais il est en fait parfois plus judicieux d’utiliser une m\u00e9thode impl\u00e9mentant une mise \u00e0 jour (commande ALTER), qui peut pr\u00e9senter des avantages non n\u00e9gligeables.<\/p>\n

Pour ce petit tour du propri\u00e9taire, nous nous appuierons sur l’exemple d’une proc\u00e9dure stock\u00e9e que nous cherchons \u00e0 mettre \u00e0 jour, mais les remarques seraient aussi valable pour un d\u00e9clencheur (TRIGGER), une vue (VIEW) ou encore d’autre types de modules de code.<\/p>\n

Supposons donc que nous avons une proc\u00e9dure stock\u00e9e existante que nous cherchons \u00e0 mettre \u00e0 jour. Par exemple, la proc\u00e9dure dbo.uspLogError de la base de donn\u00e9es AdventureWorks 2012 (voir ici<\/a>\u00a0pour t\u00e9l\u00e9charger les bases exemples de SQL Server 2012).\u00a0Dans le contexte d’une modification en environnement de d\u00e9veloppement destin\u00e9e \u00e0 \u00eatre propag\u00e9e en environnement de recette puis de production, nous devons g\u00e9n\u00e9rer un script de cr\u00e9ation de la proc\u00e9dure.<\/p>\n

Utilisons donc les outils int\u00e9gr\u00e9s \u00e0 SQL Server Management Studio.<\/p>\n

\"\"<\/a><\/p>\n

On obtient un script qui dans un premier temps supprimer la proc\u00e9dure stock\u00e9e si elle existe, et ensuite la cr\u00e9e d’apr\u00e8s le code source correspondant.<\/p>\n

USE [AdventureWorks2012]\r\nGO\r\n\r\nIF  EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'PROCEDURE',N'uspLogError', N'PARAMETER',N'@ErrorLogID'))\r\nEXEC 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'\r\n\r\nGO\r\n\r\nIF  EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'PROCEDURE',N'uspLogError', NULL,NULL))\r\nEXEC sys.sp_dropextendedproperty @name=N'MS_Description' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'uspLogError'\r\n\r\nGO\r\n\r\n\/****** Object:  StoredProcedure [dbo].[uspLogError]    Script Date: 29\/07\/2012 17:19:55 ******\/\r\nIF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspLogError]') AND type in (N'P', N'PC'))\r\nDROP PROCEDURE [dbo].[uspLogError]\r\nGO\r\n\r\n\/****** Object:  StoredProcedure [dbo].[uspLogError]    Script Date: 29\/07\/2012 17:19:55 ******\/\r\nSET ANSI_NULLS ON\r\nGO\r\n\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\n\r\nIF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspLogError]') AND type in (N'P', N'PC'))\r\nBEGIN\r\nEXEC dbo.sp_executesql @statement = N'\r\n\r\n-- uspLogError logs error information in the ErrorLog table about the \r\n-- error that caused execution to jump to the CATCH block of a \r\n-- TRY...CATCH construct. This should be executed from within the scope \r\n-- of a CATCH block otherwise it will return without inserting error \r\n-- information. \r\nCREATE PROCEDURE [dbo].[uspLogError] \r\n    @ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted\r\nAS                               -- by uspLogError in the ErrorLog table\r\nBEGIN\r\n    SET NOCOUNT ON;\r\n\r\n    -- Output parameter value of 0 indicates that error \r\n    -- information was not logged\r\n    SET @ErrorLogID = 0;\r\n\r\n    BEGIN TRY\r\n        -- Return if there is no error information to log\r\n        IF ERROR_NUMBER() IS NULL\r\n            RETURN;\r\n\r\n        -- Return if inside an uncommittable transaction.\r\n        -- Data insertion\/modification is not allowed when \r\n        -- a transaction is in an uncommittable state.\r\n        IF XACT_STATE() = -1\r\n        BEGIN\r\n            PRINT ''Cannot log error since the current transaction is in an uncommittable state. '' \r\n                + ''Rollback the transaction before executing uspLogError in order to successfully log error information.'';\r\n            RETURN;\r\n        END\r\n\r\n        INSERT [dbo].[ErrorLog] \r\n            (\r\n            [UserName], \r\n            [ErrorNumber], \r\n            [ErrorSeverity], \r\n            [ErrorState], \r\n            [ErrorProcedure], \r\n            [ErrorLine], \r\n            [ErrorMessage]\r\n            ) \r\n        VALUES \r\n            (\r\n            CONVERT(sysname, CURRENT_USER), \r\n            ERROR_NUMBER(),\r\n            ERROR_SEVERITY(),\r\n            ERROR_STATE(),\r\n            ERROR_PROCEDURE(),\r\n            ERROR_LINE(),\r\n            ERROR_MESSAGE()\r\n            );\r\n\r\n        -- Pass back the ErrorLogID of the row inserted\r\n        SET @ErrorLogID = @@IDENTITY;\r\n    END TRY\r\n    BEGIN CATCH\r\n        PRINT ''An error occurred in stored procedure uspLogError: '';\r\n        EXECUTE [dbo].[uspPrintError];\r\n        RETURN -1;\r\n    END CATCH\r\nEND;\r\n\r\n' \r\nEND\r\nGO\r\n\r\nIF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'PROCEDURE',N'uspLogError', NULL,NULL))\r\nEXEC 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'\r\nGO\r\n\r\nIF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'PROCEDURE',N'uspLogError', N'PARAMETER',N'@ErrorLogID'))\r\nEXEC 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'\r\nGO<\/pre>\n
A noter toutefois un petit d\u00e9tail de configuration : pour \u00eatre s\u00fbr d’avoir un test d’existence, notamment avant l’instruction DROP, il convient de bien configurer les options du g\u00e9n\u00e9rateur de scripts en ce sens.<\/p>\n
\"\"<\/a><\/p>\n
On obtient donc un script qui ira tr\u00e8s bien pour un d\u00e9ploiement standard.<\/p>\n
N\u00e9anmoins, consid\u00e9rons le cas o\u00f9 la base cible aurait quelques attributions de droits sp\u00e9cifiques pour cette proc\u00e9dure stock\u00e9e. Par exemple :<\/p>\n
\"\"<\/a><\/p>\n
Dans ce cas, lors de l’ex\u00e9cution du script pr\u00e9c\u00e9dent (qui est quoi qu’il en soit g\u00e9n\u00e9r\u00e9 de la m\u00eame fa\u00e7on), ces droits sp\u00e9cifiques disparaissent.<\/p>\n
En effet, la suppression de la proc\u00e9dure stock\u00e9e efface de facto ces droits sp\u00e9cifiques, et donc la simple cr\u00e9ation de la proc\u00e9dure \u00ab\u00a0nouvelle version\u00a0\u00bb n’est pas suffisante pour les r\u00e9tablir.<\/p>\n
En fait, il convient d’utiliser la commande ALTER pour modifier la proc\u00e9dure stock\u00e9e. De fait, il n’y aura pas de modification des diff\u00e9rences habilitations sp\u00e9cifiques.<\/p>\n
-- uspLogError logs error information in the ErrorLog table about the \r\n-- error that caused execution to jump to the CATCH block of a \r\n-- TRY...CATCH construct. This should be executed from within the scope \r\n-- of a CATCH block otherwise it will return without inserting error \r\n-- information. \r\nALTER PROCEDURE [dbo].[uspLogError] \r\n    @ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted\r\nAS                               -- by uspLogError in the ErrorLog table\r\nBEGIN\r\n    SET NOCOUNT ON;\r\n\r\n    -- Output parameter value of 0 indicates that error \r\n    -- information was not logged\r\n    SET @ErrorLogID = 0;\r\n\r\n    BEGIN TRY\r\n        -- Return if there is no error information to log\r\n        IF ERROR_NUMBER() IS NULL\r\n            RETURN;\r\n\r\n        -- Return if inside an uncommittable transaction.\r\n        -- Data insertion\/modification is not allowed when \r\n        -- a transaction is in an uncommittable state.\r\n        IF XACT_STATE() = -1\r\n        BEGIN\r\n            PRINT ''Cannot log error since the current transaction is in an uncommittable state. '' \r\n                + ''Rollback the transaction before executing uspLogError in order to successfully log error information.'';\r\n            RETURN;\r\n        END\r\n\r\n        INSERT [dbo].[ErrorLog] \r\n            (\r\n            [UserName], \r\n            [ErrorNumber], \r\n            [ErrorSeverity], \r\n            [ErrorState], \r\n            [ErrorProcedure], \r\n            [ErrorLine], \r\n            [ErrorMessage]\r\n            ) \r\n        VALUES \r\n            (\r\n            CONVERT(sysname, CURRENT_USER), \r\n            ERROR_NUMBER(),\r\n            ERROR_SEVERITY(),\r\n            ERROR_STATE(),\r\n            ERROR_PROCEDURE(),\r\n            ERROR_LINE(),\r\n            ERROR_MESSAGE()\r\n            );\r\n\r\n        -- Pass back the ErrorLogID of the row inserted\r\n        SET @ErrorLogID = @@IDENTITY;\r\n    END TRY\r\n    BEGIN CATCH\r\n        PRINT ''An error occurred in stored procedure uspLogError: '';\r\n        EXECUTE [dbo].[uspPrintError];\r\n        RETURN -1;\r\n    END CATCH\r\nEND;<\/pre>\n
Mais alors, comment faire pour que ce code soit passe-partout, c’est \u00e0 dire qu’il cr\u00e9e la proc\u00e9dure si celle-ci n’existe pas ?<\/p>\n
Et bien tout simplement en cr\u00e9ant une coquille vide si besoin, en positionnant le code ci-dessous avant le ALTER :<\/p>\n
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspLogError]') AND type in (N'P', N'PC'))\r\nexec sp_executesql N'CREATE PROCEDURE [dbo].[uspLogError] AS RETURN;'\r\nGO<\/pre>\n
Au final, chacun choisira donc son mode de d\u00e9ploiement, mais j’ai personnellement une certaine pr\u00e9f\u00e9rence pour le couple CREATE\/ALTER, par opposition au couple standard DROP\/CREATE…<\/p>\n","protected":false},"excerpt":{"rendered":"
Pour mettre \u00e0 jour une proc\u00e9dure stock\u00e9e, une vue ou encore d’autres objets SQL Server disposant d’une d\u00e9finition sous forme T-SQL, une solution classique consiste \u00e0 supprimer la d\u00e9finition actuelle de l’objet (DROP) et \u00e0 recr\u00e9er ensuite l’objet avec sa … Continuer la lecture →<\/span><\/a><\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-411","post","type-post","status-publish","format-standard","hentry","category-article_sql"],"_links":{"self":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/411","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/comments?post=411"}],"version-history":[{"count":26,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/411\/revisions"}],"predecessor-version":[{"id":1942,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/411\/revisions\/1942"}],"wp:attachment":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/media?parent=411"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/categories?post=411"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/tags?post=411"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}