studio - use procedure sql server




Como verificar se existe um procedimento armazenado antes de criá-lo (12)

Eu tenho um script SQL que tem que ser executado toda vez que um cliente executa a funcionalidade de "gerenciamento de banco de dados". O script inclui a criação de procedimentos armazenados no banco de dados do cliente. Alguns desses clientes podem já ter o procedimento armazenado ao executar o script e outros não. Eu preciso ter os procedimentos armazenados em falta adicionados ao banco de dados do cliente, mas não importa o quanto eu tento dobrar a sintaxe T-SQL, recebo

CREATE / ALTER PROCEDURE 'deve ser a primeira instrução em um lote de consulta

Eu já li isso antes de criar obras, mas não gosto de fazer assim.

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

CREATE PROCEDURE MyProc
...

Como posso adicionar verificar a existência de um procedimento armazenado e criá-lo se ele não existir, mas alterá-lo se ele existir?


** A maneira mais simples de eliminar e recriar um proc armazenado no T-Sql é **

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


Aqui está o script que eu uso. Com isso, evito deixar cair desnecessariamente e recriar os procedimentos armazenados.

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

Aqui está um método e algum raciocínio por trás de usá-lo dessa maneira. Não é tão bonito editar o procedimento armazenado, mas existem prós e contras ...

ATUALIZAÇÃO: Você também pode envolver toda essa chamada em uma TRANSACTION. Incluindo muitos procedimentos armazenados em uma única transação que pode ser confirmada ou toda a reversão. Outra vantagem do envolvimento em uma transação é que o procedimento armazenado sempre existe para outras conexões SQL, desde que elas não usem o nível de isolamento de transação READ UNCOMMITTED!

1) Evitar alterações apenas como uma decisão de processo. Nossos processos são sempre SE EXISTE DEIXAR DE CRIAR. Se você fizer o mesmo padrão de assumir que o novo PROC é o proc desejado, o catering para alters é um pouco mais difícil porque você teria um IF EXISTS ALTER ELSE CREATE.

2) Você precisa colocar CREATE / ALTER como a primeira chamada em um lote, para que não seja possível agrupar uma sequência de atualizações de procedimentos em uma transação fora do SQL dinâmico. Basicamente, se você quiser executar uma pilha inteira de atualizações de procedimentos ou recuperá-las sem restaurar um backup de banco de dados, essa é uma maneira de fazer tudo em um único lote.

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


Eu aparentemente não tenho a reputação necessária para votar ou comentar, mas eu só queria dizer que a resposta de Geoff usando EXEC (sp_executesql pode ser melhor) é definitivamente o caminho a percorrer. A eliminação e recriação do procedimento armazenado realiza o trabalho no final, mas há um momento no tempo em que o procedimento armazenado não existe, e isso pode ser muito ruim, especialmente se isso for algo que será executar repetidamente. Eu estava tendo todos os tipos de problemas com o meu aplicativo porque um thread em segundo plano estava fazendo um IF EXISTS DROP ... CREATE ao mesmo tempo outro segmento estava tentando usar o procedimento armazenado.


Eu percebo que isso já foi marcado como respondido, mas nós costumávamos fazer assim:

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

Apenas para evitar o procedimento.


Eu sei que é um post muito antigo, mas desde que isso aparece nos resultados de pesquisa superiores, portanto, adicionando a atualização mais recente para aqueles que usam o SQL Server 2016 SP1 -

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

Isso cria um Procedimento Armazenado, se ainda não existir, mas altera-o, se existir.

Reference


Eu tive o mesmo erro. Eu sei que este segmento já está praticamente morto, mas quero definir outra opção além de "procedimento anônimo".

Eu resolvi assim:

  1. Verifique se o procedimento armazenado 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. No entanto, o "CREATE/ALTER PROCEDURE' must be the first statement in a query batch" ainda está lá. Eu resolvi assim:

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE -- view procedure function or anything you want ...
  3. Eu acabo com este código:

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

No SQL Server 2008 em diante, você pode usar " INFORMATION_SCHEMA.ROUTINES "

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

Verifique se existe para procedimento armazenado

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

Marque IF Exist for Trigger, Função também clicando no link abaixo http://www.gurujipoint.com/2017/05/check-if-exist-for-trigger-function-and.html


Você pode executar o código procedural em qualquer lugar em que você possa executar uma consulta.

Basta copiar tudo depois do AS :

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

Esse código faz exatamente as mesmas coisas que um proc armazenado faria, mas não é armazenado no lado do banco de dados.

Isso é muito parecido com o que é chamado de procedimento anônimo em PL/SQL .

Atualizar:

O título da sua pergunta é um pouco confuso.

Se você só precisa criar um procedimento se ele não existir, então seu código está bem.

Aqui está o que o SSMS produz no script de criação:

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

Atualizar:

Exemplo de como fazer isso quando incluir o esquema:

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

No exemplo acima, dbo é o esquema.

Atualizar:

No SQL Server 2016+, você pode apenas fazer

CREATE OR ALTER PROCEDURE dbo.MyProc





tsql