Início / dba / Perguntas / 322580
Accepted
Thomas Franz
Asked: 2023-01-26 09:42:47 +0800 CST

MS SQL Server: Criação dinâmica de procedimento/função/visualização/gatilho em outro banco de dados (sp_executesql)

  • 772

Problema:

  1. você quer/precisa usar SQL dinâmico para criar procedimento/função/view/trigger em outros bancos de dados que não o atual
  2. você não pode especificar o nome do banco de dados na instrução, CREATE VIEW tempdb.dbo.v_test AS SELECT 1; falhará com o Erro 166: 'CREATE/ALTER VIEW' não permite especificar o nome do banco de dados como um prefixo para o nome do objeto.
  3. a instrução CREATE(ou ALTER) precisa ser a primeira no lote, caso contrário, você obterá o erro 111: 'CREATE ...' deve ser a primeira instrução em um lote de consulta. . Por esta razão, você não pode simplesmente escrever USE <database>antes de CREATE:
DECLARE @create_sql NVARCHAR(max);
SET @create_sql = CONCAT('USE tempdb', CHAR(13) + CHAR(10),
                         'CREATE VIEW dbo.v_test AS SELECT 1 AS n;'
                        );
EXEC (@create_sql) -- fails
-- or
EXEC sys.sp_executesql @create_sql -- fails too
  1. você não pode usar GOdentro de uma instrução EXEC()or EXEC sys.sp_executesql
    , porque não está no padrão SQL (apenas uma palavra fictícia configurável usada dentro do SSMS e várias outras ferramentas SQL), então você não pode simplesmente adicioná-lo entre o USE e o comando CREATE em o script acima como você faria no SSMS ao criar um script de implantação "normal"
  2. o procedimento interno sys.sp_executesqlexiste em cada banco de dados e executa o comando fornecido em seu próprio banco de dados, mas pode haver muitos (por exemplo, centenas) bancos de dados, de modo que você não pode realmente usar várias IFinstruções para "codificar" onde deseja executá-lo
sql-server

1 respostas

  1. Best Answer

    Solução:

    Você tem que usar uma chamada aninhada três vezes.

    1. você chama sys.sp_executesqlno banco de dados atual (por exemplo, mestre)
    2. isso chama sys.sp_executesqlno banco de dados de destino (por exemplo, tempdb)
    3. isso finalmente chama um EXEC (@create_sql) para criar o procedimento/gatilho/função/gatilho etc.

    Como a CREATEinstrução é passada como variável e não como string, você não precisa se preocupar com aspas, caracteres especiais ou injeção de sql - exceto pela inicial CREATE(@create_sql no exemplo), é claro

    Exemplo:

    USE master;
DECLARE @dest_db_name sysname       = 'tempdb'
      , @create_sql   NVARCHAR(max) = 'CREATE VIEW dbo.v_test AS SELECT 1 AS n;'
      , @dummy_cmd    NVARCHAR(500)
;
                                                             -- second exec        -- third exec
SET @dummy_cmd =  CONCAT('EXEC ', QUOTENAME(@dest_db_name), '.sys.sp_executesql N''EXEC (@create_sql)'', N''@create_sql NVARCHAR(MAX)'', @create_sql = @create_sql;')
     -- first exec 
EXEC sys.sp_executesql @dummy_cmd, N'@create_sql NVARCHAR(MAX)', @create_sql = @create_sql

-- control
SELECT *  FROM tempdb.dbo.v_test

-- DROP can be used with other statements in the same batch, so it is much easier and doesn't need special treatment
SET @dummy_cmd = 'USE tempdb;' + CHAR(13) + CHAR(10) + 'DROP VIEW IF EXISTS dbo.v_test;'
EXEC (@dummy_cmd)
    • 1

relate perguntas