Jak sprawdzić, czy procedura przechowywana istnieje przed jej utworzeniem

Mam skrypt SQL, który musi być uruchamiany za każdym razem, gdy klient wykonuje funkcję „zarządzania bazą danych”. Skrypt obejmuje tworzenie procedur przechowywanych w bazie danych klienta. Niektóre z tych klientów mogą już mieć procedurę przechowywaną po uruchomieniu skryptu, a niektóre nie. Potrzebuję dodać brakujące procedury przechowywane do bazy danych klienta, ale nie ma znaczenia, ile próbuję zgiąć składnię T-SQL, otrzymuję

CREATE / ALTER PROCEDURE ”musi być pierwszą instrukcją w partii zapytania

Przeczytałem to upuszczenie przed utworzeniem prac, ale nie lubię robić tego w ten sposób.

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

CREATE PROCEDURE MyProc
...

Jak mogę dodać sprawdzanie istnienia procedury składowanej i utworzyć ją, jeśli ona nie istnieje, ale zmienić ją, jeśli ona istnieje?

Możesz uruchomić kod proceduralny w dowolnym miejscu, w którym możesz uruchomić zapytanie.

Po prostu skopiuj wszystko po AS:

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

Ten kod robi dokładnie to samo, co zrobiłby przechowywany proc, ale nie jest przechowywany po stronie bazy danych.

To bardzo przypomina tak zwaną procedurę anonimową PL/SQL.

Aktualizacja:

Twój tytuł pytania jest trochę mylący.

Jeśli musisz utworzyć procedurę tylko wtedy, gdy ona nie istnieje, kod jest w porządku.

Oto, co SSMSwyprowadza skrypt tworzenia:

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

Aktualizacja:

Przykład, jak to zrobić, dołączając schemat:

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

W powyższym przykładzie dbo jest schematem.

Aktualizacja:

W SQL Server 2016+ możesz to zrobić

CREATE OR ALTER PROCEDURE dbo.MyProc

Zdaję sobie sprawę, że zostało to już oznaczone jako odebrane, ale robiliśmy to w ten sposób:

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
  ....

Żeby nie upuścić procedury.

Jeśli szukasz najprostszego sposobu sprawdzenia istnienia obiektu bazy danych przed jego usunięciem, oto jeden ze sposobów (przykład wykorzystuje SPROC, tak jak twój przykład powyżej, ale można go zmodyfikować dla tabel, indeksów itp.):

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

Jest to szybkie i eleganckie, ale musisz upewnić się, że masz unikalne nazwy obiektów dla wszystkich typów obiektów, ponieważ nie bierze tego pod uwagę.

Mam nadzieję, że to pomoże!

Wiem, że chcesz „zmienić procedurę, jeśli istnieje i usunąć ją tylko, jeśli nie istnieje”, ale uważam, że łatwiej jest po prostu zawsze upuścić procedurę, a następnie ponownie ją utworzyć. Oto jak porzucić procedurę tylko wtedy, gdy już istnieje:

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

Drugi parametr mówi OBJECT_IDtylko spojrzeć na obiektach z object_type = 'P', procedur, które są przechowywane:

AF = funkcja agregująca (CLR)

C = ograniczenie CHECK

D = DOMYŚLNY (ograniczenie lub samodzielny)

F = KLUCZ ZAGRANICZNY

FN = funkcja skalarna SQL

FS = funkcja skalarna zestawu (CLR)

FT = funkcja wyceniana w tabeli asemblera (CLR)

IF = SQL wbudowana funkcja wartościowana w tabeli

IT = wewnętrzny stół

P = procedura przechowywana SQL

PC = procedura składowana zestawu (CLR)

PG = przewodnik po planach

PK = ograniczenie klucza podstawowego

R = Reguła (w starym stylu, samodzielna)

RF = procedura filtra replikacji

S = Systemowa tabela bazowa

SN = synonim

SO = Obiekt sekwencji

TF = funkcja tabeli z wartościami SQL

TR = wyzwalacz

Możesz uzyskać pełną listę opcji poprzez:

SELECT name 
FROM master..spt_values
WHERE type = 'O9T'

Począwszy od SQL SERVER 2016 możesz używać nowego DROP PROCEDURE IF EXISTS.
DROP { PROC | PROCEDURE } [ IF EXISTS ] { [ schema_name. ] procedure } [ ,...n ]

Odniesienie: https://msdn.microsoft.com/en-us/library/ms174969.aspx

Wiem, że jest to bardzo stary post, ale ponieważ pojawia się w najlepszych wynikach wyszukiwania, dlatego dodaje najnowszą aktualizację dla osób korzystających z programu SQL Server 2016 z dodatkiem SP1 -

create or alter procedure procTest
as
begin
 print (1)
end;
go

Powoduje to utworzenie procedury składowanej, jeśli jeszcze nie istnieje, ale zmienia ją, jeśli istnieje.

Odniesienie

DROP IF EXISTS to nowa funkcja programu SQL Server 2016

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016/

DROP  PROCEDURE IF EXISTS dbo.[procname]

Miałem ten sam błąd. Wiem, że ten wątek jest już prawie martwy, ale chcę ustawić inną opcję oprócz „anonimowej procedury”.

Rozwiązałem to w ten sposób:

1. Sprawdź, czy istnieje procedura składowana:

   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. Jednak "CREATE/ALTER PROCEDURE' must be the first statement in a query batch"nadal tam jest. Rozwiązałem to w ten sposób:

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

3. Kończę z tym kodem:

   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 ...

Oto metoda i uzasadnienie korzystania z niej w ten sposób. Edytowanie przechowywanych proc nie jest tak ładne, ale są plusy i minusy ...

AKTUALIZACJA: Możesz także zawrzeć całe połączenie w TRANSAKCJI. W tym wiele procedur przechowywanych w jednej transakcji, które mogą wszystkie zatwierdzić lub wszystkie wycofać. Kolejną zaletą zawijania transakcji jest to, że procedura przechowywana zawsze istnieje dla innych połączeń SQL, o ile nie używają one poziomu izolacji ODCZYTAJ NIEZGODNĄ Z transakcją!

1) Aby uniknąć zmian po prostu jako decyzja procesowa. Nasze procesy muszą zawsze odbywać się, JEŚLI ISTNIEJE UPADEK, A następnie TWORZENIE. Jeśli zrobisz to samo, zakładając, że nowy PROC jest pożądanym proc, catering na zmiany jest nieco trudniejszy, ponieważ miałbyś JEŚLI ISTNIEJE INNE STWORZENIE.

2) Musisz umieścić CREATE / ALTER jako pierwsze wywołanie wsadowe, aby nie można było zawrzeć sekwencji aktualizacji procedur w transakcji poza dynamicznym SQL. Zasadniczo, jeśli chcesz uruchomić cały stos aktualizacji procedur lub przywrócić je wszystkie bez przywracania kopii zapasowej DB, jest to sposób na zrobienie wszystkiego w jednej partii.

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

W Sql server 2008 r. Możesz użyć „ INFORMATION_SCHEMA.ROUTINES”

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES 
  WHERE ROUTINE_NAME = 'MySP'
        AND ROUTINE_TYPE = 'PROCEDURE') 

Najwyraźniej nie mam reputacji wymaganej do głosowania lub komentowania, ale chciałem tylko powiedzieć, że odpowiedź Geoffa przy użyciu EXEC (sp_executesql może być lepsza) jest zdecydowanie najlepszym rozwiązaniem. Porzucenie, a następnie ponowne utworzenie procedury przechowywanej powoduje, że zadanie zostało wykonane w końcu, ale jest taki moment, że procedura przechowywana w ogóle nie istnieje, a to może być bardzo złe, szczególnie jeśli będzie to coś, co będzie biegać wielokrotnie. Miałem wiele problemów z aplikacją, ponieważ wątek w tle wykonywał IF IFIST DROP ... CREATE w tym samym czasie inny wątek próbował użyć procedury składowanej.

** Najprostszym sposobem na upuszczenie i odtworzenie przechowywanego proc w T-Sql jest **

Use DatabaseName
go
If Object_Id('schema.storedprocname') is not null
begin
   drop procedure schema.storedprocname
end
go

create procedure schema.storedprocname
as

begin
end

Oto skrypt, którego używam. Dzięki temu unikam niepotrzebnego upuszczania i odtwarzania przechowywanych procesów.

IF NOT EXISTS (
    SELECT *
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[uspMyProcedure]')
    )
BEGIN
  EXEC sp_executesql N'CREATE PROCEDURE [dbo].[uspMyProcedure] AS select 1'
END
GO

ALTER PROCEDURE [dbo].[uspMyProcedure] 
    @variable1 INTEGER  
AS
BEGIN
   -- Stored procedure logic
END

Sprawdź, czy istnieje procedura składowana

IF EXISTS (SELECT * FROM sys.objects 
            WHERE object_id = OBJECT_ID
             (N'[Schema].[Procedure_Name]') AND type IN (N'P', N'PC'))
BEGIN
       DROP PROCEDURE [Schema].[Procedure_Name]
       Print('Proceudre dropped => [Schema].[Procedure_Name]')
END

Sprawdź IF Exist dla Trigger, funkcja również klikając poniżej link http://www.gurujipoint.com/2017/05/check-if-exist-for-trigger-function-and.html

dlaczego nie pójdziesz w prosty sposób

    IF EXISTS(SELECT * FROM sys.procedures WHERE NAME LIKE 'uspBlackListGetAll')
    BEGIN
         DROP PROCEDURE uspBlackListGetAll
    END
    GO

    CREATE Procedure uspBlackListGetAll

..........

Oprócz odpowiedzi z @Geoff stworzyłem proste narzędzie, które generuje plik SQL, który zawiera instrukcje dla procedur przechowywanych, widoków, funkcji i wyzwalaczy.

Zobacz MyDbUtils @ CodePlex .

Zastanawiam się! Dlaczego nie piszę całego zapytania jak

GO
create procedure [dbo].[spAddNewClass] @ClassName varchar(20),@ClassFee int
as
begin
insert into tblClass values (@ClassName,@ClassFee)
end

GO
create procedure [dbo].[spAddNewSection] @SectionName varchar(20),@ClassID       int
as
begin
insert into tblSection values(@SectionName,@ClassID)
end

Go
create procedure test
as
begin 
select * from tblstudent
end

Wiem już, że pierwsze dwie procedury już istnieją. SQL uruchomi kwerendę poda błąd pierwszych dwóch procedur, ale nadal utworzy ostatnią procedurę. SQl sam zajmuje się tym, co już istnieje. To właśnie zawsze robię wszystkim klienci!

UTWÓRZ Procedura, JEŚLI NIE ISTNIEJE „Twoja nazwa-proc” () ROZPOCZNIJ ... KONIEC