Jak wykryć, czy procedura składowana już istnieje

Muszę napisać skrypt wdrażania, który będzie działał, jeśli procedura składowana istnieje lub nie istnieje. tzn. jeśli istnieje, muszę to zmienić, w przeciwnym razie utworzyć.

Jak mogę to zrobić w sql.

Używam SQL Server 2005

Jeśli upuścisz i utworzysz procedurę, utracisz ustawienia zabezpieczeń. Może to zdenerwować administratora lub całkowicie zepsuć aplikację.

Tworzę trywialną procedurę składowaną, jeśli jeszcze nie istnieje. Następnie możesz ZMIENIĆ procedurę składowaną według własnych upodobań.

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

W ten sposób ustawienia bezpieczeństwa, komentarze i inne meta deta przetrwają wdrożenie.

Najczystszym sposobem jest sprawdzenie jego istnienia, porzucenie go, jeśli istnieje, a następnie odtworzenie go. Nie możesz osadzić instrukcji „create proc” wewnątrz instrukcji IF. To powinno ładnie wystarczyć:

IF OBJECT_ID('MySproc', 'P') IS NOT NULL
DROP PROC MySproc
GO

CREATE PROC MySproc
AS
BEGIN
    ...
END

Jeśli masz do czynienia tylko z procedurami składowanymi, najłatwiejszą rzeczą do zrobienia jest prawdopodobnie porzucenie procesu, a następnie jego ponowne utworzenie. Możesz wygenerować cały kod, aby to zrobić, używając kreatora Generuj skrypty w 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...

Od SQL Server 2016 CTP3można korzystać z nowych Die oświadczenia zamiast dużych IFowijarki

Składnia:

DROP {PROC | PROCEDURA} [JEŚLI ISTNIEJE] {[nazwa_schematu. ] procedura} [, ... n]

Pytanie:

DROP PROCEDURE IF EXISTS usp_name

Więcej informacji tutaj

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

gdzie xxxjest nazwa proc

Możesz napisać zapytanie w następujący sposób:

IF OBJECT_ID('ProcedureName','P') IS NOT NULL
    DROP PROC ProcedureName
GO

CREATE PROCEDURE [dbo].[ProcedureName]
...your query here....

Aby być bardziej szczegółowym w powyższej składni:
OBJECT_ID to unikalny numer identyfikacyjny obiektu w bazie danych, używany wewnętrznie przez SQL Server. Ponieważ przekazujemy nazwę procedury, a następnie typ obiektu P, który mówi SQL Server, że należy znaleźć obiekt o nazwie nazwa_procedury, który jest typu procedura, tj. P

To zapytanie znajdzie procedurę i jeśli jest dostępna, porzuci ją i utworzy nową.

Aby uzyskać szczegółowe informacje na temat OBJECT_ID i typów obiektów, odwiedź: SYS.Objects

Oprócz tego, co już zostało powiedziane, chciałbym również dodać inne podejście i zalecać stosowanie strategii wdrażania skryptów różnicowych. Zamiast tworzyć skrypt pełnostanowy, który zawsze sprawdza bieżący stan i działa w oparciu o ten stan, należy wdrożyć serię skryptów bezstanowych, które aktualizują dobrze znane wersje . Użyłem tej strategii i bardzo się opłaca, ponieważ moje skrypty wdrożeniowe są teraz wszystkie wolne od „IF”.

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

Mam przechowywany proces, który pozwala klientowi przedłużyć walidację, jeśli istnieje, nie chcę go zmieniać, jeśli nie, chcę go utworzyć, najlepszy sposób, jaki znalazłem:

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

Poniższy kod sprawdzi, czy procedura składowana już istnieje, czy nie.

Jeśli istnieje, ulegnie zmianie, jeśli nie istnieje, utworzy nową procedurę składowaną:

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

Lepszą opcją może być użycie narzędzia takiego jak Red-Gate SQL Compare lub SQL Examiner, aby automatycznie porównać różnice i wygenerować skrypt migracji.