risposta-alla-domanda-sullo-sviluppo-web-bd.com

Creare una stored procedure se non esiste già

Voglio verificare se esiste una lista di stored procedure. Voglio che tutto questo sia fatto in 1 script, uno per uno. Finora ho questo formato:

USE [myDatabase]
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_1')
BEGIN
CREATE PROCEDURE sp_1
AS
.................
END
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_2')
BEGIN
CREATE PROCEDURE sp_2
AS
.................
END
GO

e così via. Tuttavia, sto ricevendo il seguente errore: 

Sintassi errata vicino alla parola chiave "Procedura".

Perché non è quello che sto facendo funzionare correttamente?

11
Sean Smyth

CREATE PROCEDURE deve essere la prima dichiarazione nel batch. Di solito faccio qualcosa del genere:

IF EXISTS (
        SELECT type_desc, type
        FROM sys.procedures WITH(NOLOCK)
        WHERE NAME = 'procname'
            AND type = 'P'
      )
     DROP PROCEDURE dbo.procname
GO

CREATE PROC dbo.procname

AS
....

    GO
    GRANT EXECUTE ON dbo.MyProc TO MyUser 

(non dimenticare le dichiarazioni di sovvenzione poiché saranno perse se si ricrea il proprio proc) 

Un'altra cosa da considerare quando si distribuiscono stored procedure è che una goccia può avere successo e una creazione fallire. Scrivo sempre i miei script SQL con un rollback in caso di problemi. Assicurati di non eliminare accidentalmente il codice di commit/rollback alla fine, altrimenti il ​​tuo DBA potrebbe tirarti un calcio nella trachea :) 

BEGIN TRAN 
IF EXISTS (
       SELECT type_desc, type
       FROM sys.procedures WITH(NOLOCK)
       WHERE NAME = 'myProc'
           AND type = 'P'
     )
DROP PROCEDURE myProc GO
CREATE PROCEDURE myProc

AS
   --proc logic here

GO
-- BEGIN DO NOT REMOVE THIS CODE (it commits or rolls back the stored procedure drop) IF EXISTS (
       SELECT 1
       FROM sys.procedures WITH(NOLOCK)
       WHERE NAME = 'DatasetDeleteCleanup'
           AND type = 'P'
     )
COMMIT TRAN
ELSE
ROLLBACK TRAN
-- END DO NOT REMOVE THIS CODE
23
Code Magician

Un idioma che ho usato ultimamente mi piace molto è:

if exists (select 1 from sys.objects where object_id = object_id('dbo.yourProc'))
   set noexec on
go
create procedure dbo.yourProc as
begin
   select 1 as [not yet implemented]
end
go
set noexec off
alter procedure dbo.yourProc as
begin
   /*body of procedure here*/
end

In sostanza, stai creando uno stub se la procedura non esiste e quindi modificando lo stub (se è stato appena creato) o la procedura preesistente. La cosa bella di questo è che non si rilascia una procedura preesistente che elimina anche tutte le autorizzazioni. Puoi anche causare problemi con qualsiasi applicazione che capita di volerlo in quel breve istante in cui non esiste.

[Modifica 2018-02-09] - In SQL 2016 SP1, create procedure e drop procedure hanno ottenuto dello zucchero sintattico che aiuta con questo genere di cose. In particolare, ora puoi fare questo:

create or alter dbo.yourProc as
go

drop procedure if exists dbo.yourProc;

Entrambi forniscono idempotenza nella dichiarazione prevista (ad esempio, è possibile eseguirlo più volte e lo stato desiderato). Questo è come lo farei ora (supponendo che tu sia su una versione di SQL Server che lo supporta).

18
Ben Thul

So che c'è una risposta accettata, ma la risposta non risponde esattamente a ciò che la domanda originale chiede, ovvero CREARE la procedura se non esiste. Quanto segue funziona sempre e ha il vantaggio di non richiedere procedure di rilascio che possono essere problematiche se si utilizza l'autenticazione sql. 

USE [MyDataBase]
GO

IF OBJECT_ID('mySchema.myProc') IS NULL
EXEC('CREATE PROCEDURE mySchema.myProc AS SET NOCOUNT ON;')
GO

ALTER PROCEDURE mySchema.myProc
    @DeclaredParmsGoHere    DataType

AS 
   BEGIN
       DECLARE @AnyVariablesINeed    Their DataType
   SELECT myColumn FROM myTable WHERE myIndex = @IndexParm
7
Ron

Mi piace usare ALTER in modo da non perdere le autorizzazioni e se hai un errore di sintassi la vecchia versione esiste ancora:

BEGIN TRY
    --if procedure does not exist, create a simple version that the ALTER will replace.  if it does exist, the BEGIN CATCH will eliminate any error message or batch stoppage
    EXEC ('CREATE PROCEDURE AAAAAAAA AS DECLARE @A varchar(100); SET @A=ISNULL(OBJECT_NAME(@@PROCID), ''unknown'')+'' was not created!''; RAISERROR(@A,16,1);return 9999')
END TRY BEGIN CATCH END CATCH
GO

ALTER PROCEDURE AAAAAAAA 
(
     @ParamsHere varchar(10)
)
AS
PRINT 'HERE IN '+(OBJECT_NAME(@@PROCID))
GO
2
KM.
USE [myDatabase]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_1')
BEGIN
  DROP PROCEDURE sp_1
END
GO   --<-- Add a Batch Separator here



CREATE PROCEDURE sp_1
AS
.................
END
GO
2
M.Ali

Nel caso in cui si stia utilizzando SQL Server 2016, è disponibile una versione più breve per verificare se esiste il proc e quindi rilasciarlo e ricrearlo 

USE [DATABASENAME]
GO
DROP PROCEDURE IF EXISTS <proc name>
GO
CREATE PROCEDURE <proc name>
AS
-- your script here
END
GO
GRANT EXECUTE ON <proc name> TO <username>

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

1
Niladri
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetRailItems]') AND type in (N'P', N'PC'))
BEGIN 
execute ('
CREATE PROCEDURE [dbo].[spGetRailItems]  
AS  
BEGIN  

Declare @isLiftedBagsEnable bit=1;  
select @isLiftedBagsEnable=cast(DataValu as bit) from setups where scope =''Rail Setting'' and dataName = ''isLiftedBagsEnable'';

IF @isLiftedBagsEnable=1
BEGIN
    IF EXISTS (SELECT * FROM ITEMCONFIG)
    BEGIN
        SELECT [Item],[Desc] FROM ProcData WHERE Item IN (SELECT Item FROM ItemConfig) ORDER BY [Desc]
    END
    ELSE
    BEGIN
        SELECT [Item],[Desc] FROM ProcData ORDER BY [Desc]
    END
END
ELSE
BEGIN
    SELECT [Item],[Desc] FROM ProcData ORDER BY [Desc]
END

END

')
END

exec spGetRailItems;
0
Ajay Dagade