Início / dba / Perguntas / 146174
Accepted
krystah
Asked: 2016-08-09 04:24:13 +0800 CST

sp_executesql - O que há de errado com minha string de parâmetro?

  • 772

Eu me encarei cegamente com isso. Começou como um procedimento mais complexo, mas eu o reduzi ao mínimo, tentando fazê-lo funcionar. Este é o código atual:

DECLARE @sql NVarchar(MAX) = '
CREATE PROCEDURE dbo.Test
AS
BEGIN
    SELECT 1, @num;
END'

EXEC [sp_executesql] @sql, N'@num int', @num = 1;

Ele simplesmente se recusa. Vale ressaltar que, se eu executar o código sem a parte do parâmetro ( @sqlsendo o único parâmetro da sp_executesqlfunção), ele funcionará bem. Eu tentei outras alternativas de sintaxe, como

EXEC [sp_executesql] @sql, N'@num int', 1;

e

DECLARE @params NVarchar(1000) = '@num int';
EXEC [sp_executesql] @sql, @params, 1;

O que há de errado com minha especificação de parâmetro?


Editar 1 : mensagem de erro incluída

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'PROCEDURE'.
t-sql sql-server-2012

2 respostas

  1. Best Answer

    Você parece estar esperando que os parâmetros acabem sendo tratados como argumentos de interpolação de strings.

    Eles não vão.

    Uma maneira de conseguir algo semelhante ao que você deseja seria simplesmente parametrizar o procedimento armazenado

    CREATE PROCEDURE dbo.Test
@num int
AS
BEGIN
    SELECT 1, @num;
END

    Então, ao executá-lo, você fornece o valor desejado.

    Se você definitivamente deseja que o procedimento seja @numsubstituído por um codificado 1, você pode usar.

    DECLARE @num int = 1

DECLARE @sql NVarchar(MAX) = '
CREATE PROCEDURE dbo.Test
AS
BEGIN
    SELECT 1, @num;
END'

SET @sql = REPLACE(@sql, '@num', @num)

EXEC sys.sp_executesql @sql -- or just EXEC(@sql)

    Cuide da injeção de SQL se você seguir esse caminho.

    Além disso, você precisaria ter cuidado com os nomes dos espaços reservados e parâmetros e a ordem das substituições, portanto, se você tivesse espaços reservados, @numo @numberprimeiro @numberprecisaria ser substituído.

    • 2

  2. Você deve evitar tornar toda a chamada dinâmica por causa da vulnerabilidade de injeção de SQL.

    Seu procedimento também precisa aceitar o parâmetro que você está especificando em sua chamada sql dinâmica

        @sql='
    CREATE PROCEDURE dbo.Test
    @num int
    AS
    BEGIN
        SELECT 1, @num;
    END'
    exec sp_executesql @sql

    A maneira correta de parametrizar seu sql dinâmico é:

        DECLARE @params NVarchar(1000) = '@num int';
    EXEC [sp_executesql] @sql, @params, @num=1;

    A parametrização do sql dinâmico também permite que o plano no cache do procedimento seja reutilizado e é muito mais eficiente para o servidor. Se você já se deparou com sql dinâmico que não é parametrizado e não pode modificar porque está em um software de terceiros, também pode usar guias de plano.

    • -1

relate perguntas