Comment détecter si une procédure stockée existe déjà

Je dois écrire un script de déploiement qui fonctionnera si une procédure stockée existe ou n’existe pas. c’est-à-dire que s’il existe, alors je dois le modifier, sinon le créer.

Comment puis-je faire cela dans le sql.

J’utilise SQL Server 2005

Si vous supprimez et créez la procédure, vous perdrez les parameters de sécurité. Cela pourrait ennuyer votre DBA ou briser votre application.

Ce que je fais, c’est créer une procédure stockée sortingviale si elle n’existe pas encore. Après cela, vous pouvez ALTER la procédure stockée à votre convenance.

IF object_id('YourSp') IS NULL EXEC ('create procedure dbo.YourSp as select 1') GO ALTER PROCEDURE dbo.YourSp AS ... 

De cette façon, les parameters de sécurité, les commentaires et les autres méta-informations survivent au déploiement.

La manière la plus propre est de tester son existence, de la déposer si elle existe et de la recréer. Vous ne pouvez pas incorporer une instruction “create proc” dans une instruction IF. Cela devrait bien faire:

 IF OBJECT_ID('MySproc', 'P') IS NOT NULL DROP PROC MySproc GO CREATE PROC MySproc AS BEGIN ... END 

Si vous ne traitez que des procédures stockées, le plus simple est de supprimer le processus, puis de le recréer. Vous pouvez générer tout le code pour cela à l’aide de l’assistant Générer des scripts dans SQL Server.

 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[YourSproc]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[YourSproc] CREATE PROCEDURE YourSproc... 

EDIT: ne pas mettre de texte avant que les blocs de code ne le tue … oubliez toujours ça 🙂

 if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xxx]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) BEGIN CREATE PROCEDURE dbo.xxx 

où xxx est le nom de proc

À partir de SQL Server 2016 CTP3 vous pouvez utiliser de nouvelles instructions DIE au lieu de grands wrappers IF

Syntaxe:

DROP {PROC | PROCEDURE} [IF EXISTS] {[nom_schéma. ] procédure} [, … n]

Question:

 DROP PROCEDURE IF EXISTS usp_name 

Plus d’infos ici

En plus de ce qui a déjà été dit, j’aime aussi append une approche différente et préconiser l’utilisation d’une stratégie de déploiement de script différentiel. Au lieu de créer un script avec état qui vérifie toujours l’état actuel et agit en fonction de cet état, déployez-le via une série de scripts sans état qui mettent à niveau les versions connues . J’ai utilisé cette stratégie et cela porte ses fruits car mes scripts de déploiement sont désormais tous “IF” gratuits.

 IF OBJECT_ID('SPNAME') IS NULL -- Does Not Exists ELSE -- Exists 

Je suis d’accord avec Luke mais une meilleure option pourrait être d’utiliser un outil comme Red-Gate SQL Compare ou SQL Examiner pour comparer automatiquement les différences et générer un script de migration.

J’ai un processus stocké qui permet au client d’étendre la validation, s’il existe, je ne veux pas le changer, si ce n’est pas le cas, je veux le créer, le meilleur que j’ai trouvé:

 IF OBJECT_ID('ValidateRequestPost') IS NULL BEGIN EXEC ('CREATE PROCEDURE ValidateRequestPost @RequestNo VARCHAR(30), @ErrorStates VARCHAR(255) OUTPUT AS BEGIN SELECT @ErrorStates = @ErrorStates END') END 

Le code ci-dessous vérifie si la procédure stockée existe déjà ou non.

S’il existe, il changera, s’il n’existe pas, il créera une nouvelle procédure stockée pour vous:

 //syntax for Create and Alter Proc DECLARE @Create NVARCHAR(200) = 'Create PROCEDURE sp_cp_test'; DECLARE @Alter NVARCHAR(200) ='Alter PROCEDURE sp_cp_test'; //Actual Procedure DECLARE @Proc NVARCHAR(200)= ' AS BEGIN select ''sh'' END'; //Checking For Sp IF EXISTS (SELECT * FROM sysobjects WHERE id = Object_id('[dbo].[sp_cp_test]') AND Objectproperty(id, 'IsProcedure') = 1 AND xtype = 'p' AND NAME = 'sp_cp_test') BEGIN SET @Proc=@Alter + @Proc EXEC (@proc) END ELSE BEGIN SET @Proc=@Create + @Proc EXEC (@proc) END go