sp_executesql (Transact-SQL)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsPDW (Analytics Platform System)Ponto de extremidade de SQL no Microsoft FabricWarehouse no Microsoft Fabric

Executa uma instrução ou lote Transact-SQL que pode ser reutilizado muitas vezes ou que é criado dinamicamente. A instrução ou lote do Transact-SQL pode conter parâmetros inseridos.

Convenções de sintaxe de Transact-SQL

Sintaxe

Sintaxe para SQL Server, Banco de Dados SQL do Azure, Instância Gerenciada SQL do Azure, Azure Synapse Analytics e Analytics Platform System (PDW).

sp_executesql [ @stmt = ] N'statement'
[
    [ , [ @params = ] N'@parameter_name data_type [ { OUT | OUTPUT } ] [ , ...n ]' ]
    [ , [ @param1 = ] 'value1' [ , ...n ] ]
]

Os exemplos de código Transact-SQL neste artigo usam o AdventureWorks2022 banco de dados de exemplo, que você pode baixar da home page Microsoft SQL Server Samples and Community Projects .

Argumentos

@stmt [ = ] N'declaração'

Uma cadeia de caracteres Unicode que contém uma instrução ou lote Transact-SQL. @stmt deve ser uma constante Unicode ou uma variável Unicode. Expressões Unicode mais complexas, como concatenar duas cadeias de caracteres com o + operador, não são permitidas. Constantes de caracteres não são permitidas. As constantes Unicode devem ser prefixadas com um Narquivo . Por exemplo, a constante N'sp_who' Unicode é válida, mas a constante 'sp_who' de caractere não é. O tamanho da cadeia de caracteres é limitado apenas pela memória disponível do servidor de banco de dados. Em servidores de 64 bits, o tamanho da cadeia de caracteres é limitado a 2 GB, o tamanho de máximo de nvarchar(max).

@stmt pode conter parâmetros com o mesmo formato que um nome de variável. Por exemplo:

N'SELECT * FROM HumanResources.Employee WHERE EmployeeID = @IDParameter';

Cada parâmetro incluído no @stmt deve ter uma entrada correspondente na lista de definição de parâmetros @params e na lista de valores de parâmetro.

@params [ = ] N'@parameter_namedata_type [ ,... n ]'

Uma cadeia de caracteres que contém as definições de todos os parâmetros incorporados no @stmt. A cadeia de caracteres deve ser uma constante Unicode ou uma variável Unicode. Cada definição de parâmetro consiste em um nome de parâmetro e um tipo de dados. n é um espaço reservado que indica mais definições de parâmetro. Cada parâmetro especificado em @stmt deve ser definido em @params. Se a instrução ou lote Transact-SQL no @stmt não contiver parâmetros, @params não será necessário. O valor padrão para esse parâmetro é NULL.

@param1 [ = ] 'valor1'

Um valor para o primeiro parâmetro definido na cadeia de caracteres do parâmetro. O valor pode ser uma constante Unicode ou uma variável Unicode. Deve haver um valor de parâmetro fornecido para cada parâmetro incluído em @stmt. Os valores não são necessários quando a instrução Transact-SQL ou o lote no @stmt não tem parâmetros.

{ SAÍDA | SAÍDA }

Indica que o parâmetro é um parâmetro de saída. Os parâmetros text, ntext e image podem ser usados como OUTPUT parâmetros, a menos que o procedimento seja um procedimento CLR (Common Language Runtime). Um parâmetro de saída que usa a palavra-chave pode ser um espaço reservado OUTPUT do cursor, a menos que o procedimento seja um procedimento CLR.

[ ... n ]

Um espaço reservado para os valores de parâmetros extras. Os valores só podem ser constantes ou variáveis. Os valores não podem ser expressões mais complexas, como funções ou expressões criadas usando operadores.

Valores do código de retorno

0 (sucesso) ou diferente de zero (fracasso).

Conjunto de resultados

Retorna os conjuntos de resultados de todas as instruções SQL construídas na cadeia de caracteres SQL.

Comentários

sp_executesql parâmetros devem ser inseridos na ordem específica, conforme descrito na seção Sintaxe anteriormente neste artigo. Se os parâmetros forem inseridos fora de ordem, ocorrerá uma mensagem de erro.

sp_executesql tem o mesmo comportamento que EXECUTE em relação a lotes, o escopo de nomes e o contexto do banco de dados. A instrução Transact-SQL ou o lote no sp_executesqlparâmetro @stmt não é compilado até que a sp_executesql instrução seja executada. O conteúdo de @stmt é então compilado e executado como um plano de execução separado do plano de execução do lote que chamou sp_executesql. O sp_executesql lote não pode fazer referência a variáveis declaradas no lote que chama sp_executesql. Os cursores ou variáveis locais no lote sp_executesql não são visíveis para o lote que chama sp_executesql. As alterações no contexto de banco de dados duram somente até o final da instrução sp_executesql.

sp_executesql pode ser usado em vez de procedimentos armazenados para executar uma instrução Transact-SQL muitas vezes quando a alteração nos valores de parâmetro para a instrução é a única variação. Como a instrução Transact-SQL em si permanece constante e somente os valores de parâmetro são alterados, é provável que o otimizador de consulta do SQL Server reutilize o plano de execução gerado para a primeira execução. Nesse cenário, o desempenho é equivalente ao de um procedimento armazenado.

sp_executesql oferece suporte à configuração de valores de parâmetro separadamente da cadeia de caracteres Transact-SQL, conforme mostrado no exemplo a seguir.

DECLARE @IntVariable INT;
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);

/* Build the SQL string once */
SET @SQLString = N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID
       FROM AdventureWorks2022.HumanResources.Employee
       WHERE BusinessEntityID = @BusinessEntityID';
SET @ParmDefinition = N'@BusinessEntityID tinyint';
/* Execute the string with the first parameter value. */
SET @IntVariable = 197;

EXECUTE sp_executesql @SQLString,
    @ParmDefinition,
    @BusinessEntityID = @IntVariable;

/* Execute the same string with the second parameter value. */
SET @IntVariable = 109;

EXECUTE sp_executesql @SQLString,
    @ParmDefinition,
    @BusinessEntityID = @IntVariable;

Os parâmetros de saída também podem ser usados com sp_executesqlo . O exemplo a seguir recupera um título de trabalho da tabela no AdventureWorks2022 banco de dados de HumanResources.Employee exemplo e o retorna no parâmetro @max_titlede saída .

DECLARE @IntVariable INT;
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);
DECLARE @max_title VARCHAR(30);

SET @IntVariable = 197;
SET @SQLString = N'SELECT @max_titleOUT = max(JobTitle)
   FROM AdventureWorks2022.HumanResources.Employee
   WHERE BusinessEntityID = @level';
SET @ParmDefinition = N'@level TINYINT, @max_titleOUT VARCHAR(30) OUTPUT';

EXECUTE sp_executesql @SQLString,
    @ParmDefinition,
    @level = @IntVariable,
    @max_titleOUT = @max_title OUTPUT;

SELECT @max_title;

Ser capaz de substituir parâmetros em sp_executesql oferece as seguintes vantagens sobre o uso da EXECUTE instrução para executar uma cadeia de caracteres:

• Como o texto real da instrução Transact-SQL na sp_executesql cadeia de caracteres não muda entre as execuções, o otimizador de consulta provavelmente corresponde à instrução Transact-SQL na segunda execução com o plano de execução gerado para a primeira execução. Portanto, o SQL Server não precisa compilar a segunda instrução.

• A cadeia de caracteres Transact-SQL é criada apenas uma vez.

• O parâmetro numérico inteiro é especificado em seu formato nativo. A conversão para Unicode não é necessária.

Permissões

Requer associação à função pública .

Exemplos

R. Executar uma instrução SELECT

O exemplo a seguir cria e executa uma SELECT instrução que contém um parâmetro incorporado chamado @level.

EXECUTE sp_executesql
    N'SELECT * FROM AdventureWorks2022.HumanResources.Employee
    WHERE BusinessEntityID = @level',
    N'@level TINYINT',
    @level = 109;

B. Executar uma cadeia de caracteres criada dinamicamente

O exemplo a seguir mostra o uso de sp_executesql para executar uma cadeia de caracteres dinamicamente construída. O exemplo de procedimento armazenado é usado para inserir dados em um conjunto de tabelas que são usadas para particionar dados de vendas em um ano. Há uma tabela para cada mês do ano que tem o seguinte formato:

CREATE TABLE May1998Sales (
    OrderID INT PRIMARY KEY,
    CustomerID INT NOT NULL,
    OrderDate DATETIME NULL CHECK (DATEPART(yy, OrderDate) = 1998),
    OrderMonth INT CHECK (OrderMonth = 5),
    DeliveryDate DATETIME NULL,
    CHECK (DATEPART(mm, OrderDate) = OrderMonth)
);

Este procedimento armazenado de amostra constrói e executa dinamicamente uma instrução INSERT para inserir novas ordens na tabela correta. O exemplo usa a data do pedido para criar o nome da tabela que deve conter os dados e, em seguida, incorpora o nome em uma instrução INSERT.

CREATE PROCEDURE InsertSales @PrmOrderID INT,
    @PrmCustomerID INT,
    @PrmOrderDate DATETIME,
    @PrmDeliveryDate DATETIME
AS
DECLARE @InsertString NVARCHAR(500);
DECLARE @OrderMonth INT;

-- Build the INSERT statement.
SET @InsertString = 'INSERT INTO ' +
    /* Build the name of the table. */
    SUBSTRING(DATENAME(mm, @PrmOrderDate), 1, 3) +
    CAST(DATEPART(yy, @PrmOrderDate) AS CHAR(4)) + 'Sales' +
    /* Build a VALUES clause. */
    ' VALUES (@InsOrderID, @InsCustID, @InsOrdDate,' +
    ' @InsOrdMonth, @InsDelDate)';

/* Set the value to use for the order month because
   functions are not allowed in the sp_executesql parameter
   list. */
SET @OrderMonth = DATEPART(mm, @PrmOrderDate);

EXEC sp_executesql @InsertString,
    N'@InsOrderID INT, @InsCustID INT, @InsOrdDate DATETIME,
       @InsOrdMonth INT, @InsDelDate DATETIME',
    @PrmOrderID,
    @PrmCustomerID,
    @PrmOrderDate,
    @OrderMonth,
    @PrmDeliveryDate;
GO

Usar sp_executesql neste procedimento é mais eficiente do que usar EXECUTE para executar uma cadeia de caracteres. Quando sp_executesql é usado, há apenas 12 versões da cadeia de INSERT caracteres que são geradas, uma para cada tabela mensal. Com EXECUTEo , cada INSERT cadeia de caracteres é exclusiva porque os valores de parâmetro são diferentes. Embora ambos os métodos gerem o mesmo número de lotes, a semelhança das cadeias de caracteres geradas por sp_executesql torna mais provável que o otimizador de INSERT consulta reutilize planos de execução.

C. Usar o parâmetro OUTPUT

O exemplo a seguir usa um OUTPUT parâmetro para armazenar o conjunto de resultados gerado pela SELECT instrução no @SQLString parâmetro. Em seguida, são executadas duas SELECT instruções que usam o OUTPUT valor do parâmetro.

USE AdventureWorks2022;
GO

DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);
DECLARE @SalesOrderNumber NVARCHAR(25);
DECLARE @IntVariable INT;

SET @SQLString = N'SELECT @SalesOrderOUT = MAX(SalesOrderNumber)
    FROM Sales.SalesOrderHeader
    WHERE CustomerID = @CustomerID';
SET @ParmDefinition = N'@CustomerID INT,
    @SalesOrderOUT NVARCHAR(25) OUTPUT';
SET @IntVariable = 22276;

EXECUTE sp_executesql @SQLString,
    @ParmDefinition,
    @CustomerID = @IntVariable,
    @SalesOrderOUT = @SalesOrderNumber OUTPUT;

-- This SELECT statement returns the value of the OUTPUT parameter.
SELECT @SalesOrderNumber;

-- This SELECT statement uses the value of the OUTPUT parameter in
-- the WHERE clause.
SELECT OrderDate,
    TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesOrderNumber = @SalesOrderNumber;

Exemplos: Azure Synapse Analytics e PDW (Analytics Platform System)

D. Executar uma instrução SELECT

O exemplo a seguir cria e executa uma SELECT instrução que contém um parâmetro incorporado chamado @level.

EXECUTE sp_executesql
    N'SELECT * FROM AdventureWorksPDW2012.dbo.DimEmployee
    WHERE EmployeeKey = @level',
    N'@level TINYINT',
    @level = 109;

Conteúdo relacionado

• EXECUTE (Transact-SQL)
• Procedimentos armazenados do sistema (Transact-SQL)