Início / dba / Perguntas / 19007
Accepted
mehdi lotfi
Asked: 2012-06-09 20:38:17 +0800 CST

Crie uma nova função por código se ela não existir

  • 772

Eu quero criar uma nova função por script no meu banco de dados. O código do script está abaixo:

IF Exists(Select * From sys.sysobjects A Where A.name =N'fn_myfunc' and xtype=N'FN') return;

CREATE FUNCTION fn_myfunc ()
returns varchar(10)
AS Begin
...
End

Mas quando executo o script acima, o SQL Server retorna um erro:

'CREATE FUNCTION' must be the first statement in a query batch.
sql-server sql-server-2008-r2

3 respostas

  1. Best Answer

    Atualização de janeiro de 2017 - SQL Server 2016+ / Banco de Dados SQL do Azure

    O SQL Server 2016 e a versão atual do Banco de Dados SQL do Azure agora possuem a seguinte sintaxe para funções, procedimentos, tabelas, bancos de dados etc. ( DROP IF EXISTS):

    DROP FUNCTION IF EXISTS dbo.fn_myfunc;

    E o SQL Server 2016 Service Pack 1 adiciona funcionalidades ainda melhores para módulos (funções, procedimentos, gatilhos, exibições), o que significa que não há perda de permissões ou dependências ( CREATE OR ALTER):

    CREATE OR ALTER FUNCTION dbo.fn_myfunc ...

    Ambos os aprimoramentos de sintaxe podem levar a scripts muito mais simples usados ​​para controle de origem, implantações etc.

    Mas se estiver usando...

    versões mais antigas

    Você precisa fazer o que o SQL Server faz ao criar um script no Management Studio:

    IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE type = 'FN' AND name = 'fn_myfunc')
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = N'CREATE FUNCTION ...';
    EXEC sp_executesql @sql;
END

    Ou você pode dizer:

    BEGIN TRY
    DROP FUNCTION dbo.fn_myfunc;
END TRY
BEGIN CATCH
    PRINT 'Function did not exist.';
END CATCH
GO
CREATE FUNCTION...

    Ou você pode simplesmente dizer:

    DROP FUNCTION dbo.fn_myfunc;
GO
CREATE FUNCTION...

    (Aqui você receberá uma mensagem de erro se a função ainda não existir, mas o script continuará a partir do próximo GO, portanto, se a queda funcionou ou não, a função ainda será (re-)criada.)

    Observe que, se você descartar a função e recriá-la, também perderá permissões e informações de dependência potencialmente.

    • 20

  2. Você tem a opção de verificar se o objeto existe no databasee criar se não existir:

    IF OBJECT_ID('new_function', 'FN') IS NULL
BEGIN
  EXEC('CREATE FUNCTION new_function() RETURNS INT AS BEGIN RETURN 1 END');
END;
go

ALTER FUNCTION new_function() RETURNS INT AS
BEGIN

...
    • 2

  3. O erro é bastante autoexplicativo. Existem algumas maneiras de corrigi-lo.

    1. Separe o script em lotes diferentes no Management Studio usando a GOpseudo-palavra-chave e DROP/ CREATEo objeto. (Observe que a própria palavra-chave pode ser alterada nas opções do Management Studio, mas essa é a configuração de fato, então sugiro deixá-la em paz).

      Quando você executa um script (ou a parte selecionada de um script), o Management Studio separa cada parte do script entre GOs e envia sequencialmente as partes para o SQL Server como lotes separados.

    2. Use SQL dinâmico para enviar um lote separado de outro lote.

      Este é o método preferencial, pois seu script não depende de funcionalidades externas para ser executado corretamente. Por exemplo, se sua aplicação possui um programa de atualização de banco de dados, de modo geral, ele carregará um arquivo de script e o executará no servidor de destino. Ou você terá que adicionar lógica para separar os lotes como o Management Studio faz (nota: cheio de perigos), ou escrever o script de forma que todo o script possa ser executado com sucesso como um único lote.

      Como mencionado em outra resposta, você pode fazer um teste/ CREATEusando este método (ou alguma outra combinação de DROP/ CREATE, etc.). O que eu prefiro fazer é criar um objeto stub se o objeto não existir, e então usar ALTER <object>para realmente fazer a criação ou alteração. Essa abordagem não elimina dependências, como permissões ou propriedades estendidas, e não é necessário copiar/colar lógica propensa a erros para fazer CREATE/ ALTERem uma única instrução.

      Aqui está o modelo que uso para criar ou alterar uma função escalar. Vou deixar como exercício para o leitor adaptar isso para outros tipos de objetos (procs armazenados, triggers, etc.).

    IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[<schema>].[<function name>]') AND type IN ('FN', 'FS'))
    EXEC sp_executesql N'CREATE FUNCTION [<schema name>].[<function name>] (@a int) RETURNS int AS BEGIN /* Stub */ RETURN @a END'

EXEC sp_executesql N'
ALTER FUNCTION [<schema name>].[<function name>]
/* ... */
'
    • 1

relate perguntas