Comment vérifier si une procédure stockée existe avant de la créer ?

Question

J'ai un script SQL qui doit être exécuté chaque fois qu'un client exécute la fonctionnalité "gestion de base de données". Le script comprend la création de procédures stockées sur la base de données du client. Certains de ces clients pourraient déjà avoir la procédure stockée lors de l'exécution du script, et d'autres non. J'ai besoin que les procédures stockées manquantes soient ajoutées à la base de données du client, mais j'ai beau essayer de plier la syntaxe T-SQL, je n'obtiens que les résultats suivants

CREATE/ALTER PROCEDURE" doit être la première instruction d'un lot de requêtes.

J'ai lu que le fait de laisser tomber avant de créer fonctionne, mais je n'aime pas le faire de cette façon.

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'MyProc')
DROP PROCEDURE MyProc
GO

CREATE PROCEDURE MyProc
...

Comment puis-je ajouter la vérification de l'existence d'une procédure stockée et la créer si elle n'existe pas mais la modifier si elle existe ?

Answer 1

Je réalise que cette question a déjà été marquée comme répondue, mais nous avions l'habitude de faire comme ça :

IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND OBJECT_ID = OBJECT_ID('dbo.MyProc'))
   exec('CREATE PROCEDURE [dbo].[MyProc] AS BEGIN SET NOCOUNT ON; END')
GO

ALTER PROCEDURE [dbo].[MyProc] 
AS
  ....

Juste pour éviter de laisser tomber la procédure.

Answer 2

Vous pouvez exécuter du code procédural partout où vous êtes en mesure d'exécuter une requête.

Il suffit de copier tout ce qui se trouve après AS :

BEGIN
    DECLARE @myvar INT
    SELECT  *
    FROM    mytable
    WHERE   @myvar ...
END

Ce code fait exactement la même chose qu'une procédure stockée, mais il n'est pas stocké dans la base de données.

C'est un peu comme ce qu'on appelle une procédure anonyme en PL/SQL .

Mise à jour :

Le titre de votre question est un peu confus.

Si vous avez seulement besoin de créer une procédure si elle n'existe pas, alors votre code est très bien.

Voici ce que SSMS sorties dans la création de script :

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'myproc')
                    AND type IN ( N'P', N'PC' ) ) 
DROP …
CREATE …

Mise à jour :

Exemple de la façon de procéder lors de l'inclusion du schéma :

IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'[dbo].[MyProc]') 
                   and OBJECTPROPERTY(id, N'IsProcedure') = 1 )
BEGIN
    DROP PROCEDURE [dbo].[MyProc]
END

Dans l'exemple ci-dessus, dbo est le schéma.

Mise à jour :

Dans SQL Server 2016+, vous pouvez simplement faire

CREATE OR ALTER PROCEDURE dbo.MyProc

Answer 3

Si vous cherchez le moyen le plus simple de vérifier l'existence d'un objet de base de données avant de le supprimer, en voici un (l'exemple utilise un SPROC, tout comme votre exemple ci-dessus mais pourrait être modifié pour les tables, les index, etc...) :

IF (OBJECT_ID('MyProcedure') IS NOT NULL)
  DROP PROCEDURE MyProcedure
GO

C'est rapide et élégant, mais vous devez vous assurer que vous avez des noms d'objets uniques pour tous les types d'objets, car cela n'est pas pris en compte.

J'espère que cela vous aidera !

Answer 4

J'ai eu la même erreur. Je sais que ce fil de discussion est déjà bien mort mais je veux définir une autre option que "procédure anonyme".

J'ai résolu le problème de la façon suivante :

1. Vérifiez si la procédure stockée existe :

   IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='my_procedure') BEGIN
       print 'exists'  -- or watever you want
   END ELSE BEGIN
       print 'doesn''texists'   -- or watever you want
   END

2. Toutefois, le "CREATE/ALTER PROCEDURE' must be the first statement in a query batch" est toujours là. J'ai résolu le problème comme suit :

   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   
   CREATE -- view procedure function or anything you want ...

3. Je me retrouve avec ce code :

   IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('my_procedure'))
   BEGIN
       DROP PROCEDURE my_procedure
   END
   
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   
   CREATE PROCEDURE [dbo].my_procedure ...

Answer 5

Voici une méthode et un raisonnement pour l'utiliser de cette façon. Ce n'est pas aussi joli d'éditer la procédure stockée mais il y a des avantages et des inconvénients...

MISE À JOUR : Vous pouvez aussi envelopper cet appel entier dans une TRANSACTION. Cela permet d'inclure de nombreuses procédures stockées dans une seule transaction, qui peuvent toutes être validées ou annulées. Un autre avantage de l'intégration dans une transaction est que la procédure stockée existe toujours pour les autres connexions SQL tant qu'elles n'utilisent pas le niveau d'isolation de transaction READ UNCOMMITTED !

1) Éviter les altérations comme une simple décision de processus. Nos processus consistent à toujours IF EXISTS DROP THEN CREATE. Si vous faites le même schéma en supposant que le nouveau PROC est le proc désiré, la gestion des alters est un peu plus difficile parce que vous auriez un IF EXISTS ALTER ELSE CREATE.

2) Vous devez mettre CREATE/ALTER comme premier appel dans un lot, donc vous ne pouvez pas envelopper une séquence de mises à jour de procédures dans une transaction en dehors du SQL dynamique. En gros, si vous voulez exécuter toute une série de mises à jour de procédures ou les annuler toutes sans restaurer une sauvegarde de la base de données, c'est un moyen de tout faire en un seul lot.

IF NOT EXISTS (select ss.name as SchemaName, sp.name as StoredProc 
    from sys.procedures sp
    join sys.schemas ss on sp.schema_id = ss.schema_id
    where ss.name = 'dbo' and sp.name = 'MyStoredProc')
BEGIN
    DECLARE @sql NVARCHAR(MAX)

    -- Not so aesthetically pleasing part. The actual proc definition is stored
    -- in our variable and then executed.
    SELECT @sql = 'CREATE PROCEDURE [dbo].[MyStoredProc]
(
@MyParam int
)
AS
SELECT @MyParam'
    EXEC sp_executesql @sql
END