Partilhar via

sp_executesql (Transact-SQL)

  • Artigo

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics PDW (Analytics Platform System) Ponto de extremidade de análise do SQL Warehouse no Microsoft Fabric

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

Cuidado

As instruções Transact-SQL compiladas em tempo de execução podem expor aplicativos a ataques mal-intencionados. Você deve parametrizar suas consultas ao usar sp_executesqlo . Para obter mais informações, consulte SQL Injection.

Convenções de sintaxe de Transact-SQL

Sintaxe

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

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

Os exemplos de código do Transact-SQL deste artigo usa o banco de dados de exemplo AdventureWorks2022 ou AdventureWorksDW2022, que pode ser baixado da home page Microsoft SQL Server Samples and Community Projects.

Argumentos

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

Uma sequência Unicode que contém uma ou instrução Transact-SQL ou lote. @stmt deve ser uma constante Unicode ou uma variável Unicode. Expressões Unicode mais complexas, como concatenação de duas sequências 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 N. Por exemplo, a constante Unicode N'sp_who' é 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 a mesma forma de um nome de variável. Por exemplo:

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

Cada parâmetro incluído em @stmt deve ter uma entrada correspondente na lista de definições de parâmetro @params e na lista de valores de parâmetro.

@params [ = ] N'@parameter_name data_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 definições de parâmetro adicionais. Todo parâmetro especificado em @stmt deve estar definido em @params. Se a instrução ou o 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 = ] 'value1'

Um valor para o primeiro parâmetro definido na sequência 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 no @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.

{ FORA | 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 de cursor, a menos que o procedimento seja um procedimento CLR.

[ ... n ]

Um espaço reservado para os valores de parâmetros adicionais. 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 (êxito) ou não zero (falha).

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 Os parâmetros devem ser inseridos na ordem específica, conforme descrito na seção Sintaxe anteriormente neste artigo. Se os parâmetros forem inseridos na ordem incorreta, será exibida uma mensagem de erro.

sp_executesql tem o mesmo comportamento que EXECUTE em relação aos lotes, ao escopo dos nomes e ao contexto do banco de dados. A instrução ou lote Transact-SQL no parâmetro @stmt não é compilado sp_executesql até que a sp_executesql instrução seja executada. O conteúdo de @stmt é 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. Cursores ou variáveis locais no sp_executesql lote 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.

Observação

Para melhorar o desempenho, use nomes de objeto totalmente qualificados na cadeia de caracteres de instrução.

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

DECLARE @IntVariable AS INT;
DECLARE @SQLString AS NVARCHAR (500);
DECLARE @ParmDefinition AS 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_executesql. O exemplo a seguir recupera um cargo da HumanResources.Employee tabela no AdventureWorks2022 banco de dados de amostra e o retorna no parâmetro @max_titlede saída.

DECLARE @IntVariable AS INT;

DECLARE @SQLString AS NVARCHAR (500);

DECLARE @ParmDefinition AS NVARCHAR (500);

DECLARE @max_title AS 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 string:

  • Como o texto real da instrução Transact-SQL na cadeia de caracteres não é alterado entre as sp_executesql 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.

OPTIMIZED_SP_EXECUTESQL

Aplica-se a: Banco de Dados SQL do Azure

Quando a configuração no escopo do banco de dados OPTIMIZED_SP_EXECUTESQL está habilitada, o comportamento de compilação dos lotes enviados usando sp_executesql torna-se idêntico ao comportamento de compilação serializado que objetos como procedimentos armazenados e gatilhos empregam atualmente.

Quando os lotes são idênticos (excluindo quaisquer diferenças de parâmetro), a OPTIMIZED_SP_EXECUTESQL opção tenta obter um bloqueio de compilação como um mecanismo de imposição para garantir que o processo de compilação seja serializado. Esse bloqueio garante que, se várias sessões forem invocadas sp_executesql simultaneamente, essas sessões aguardarão enquanto tentam obter um bloqueio de compilação exclusivo após a primeira sessão iniciar o processo de compilação. A primeira execução de compila e insere seu plano compilado no cache de sp_executesql planos. Outras sessões abortam a espera do bloqueio de compilação e reutilizam o plano assim que ele se torna disponível.

Sem a OPTIMIZED_SP_EXECUTESQL opção, várias invocações de lotes idênticos executados por meio sp_executesql da compilação em paralelo e colocam suas próprias cópias de um plano compilado no cache de planos, que substituem ou duplicam as entradas de cache de planos em alguns casos.

Observação

Antes de habilitar a configuração no escopo do OPTIMIZED_SP_EXECUTESQL banco de dados, se as estatísticas de atualização automática estiverem habilitadas, você também deverá habilitar a opção assíncrona de estatísticas de atualização automática com a opção de configuração no escopo do banco de dados ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY. Habilitar essas duas opções pode reduzir significativamente a probabilidade de problemas de desempenho relacionados a longos tempos de compilação, juntamente com bloqueios (LCK_M_X) e WAIT_ON_SYNC_STATISTICS_REFRESH esperas excessivos e exclusivos do gerenciador de bloqueios.

OPTIMIZED_SP_EXECUTESQL está desativado por padrão. Para habilitar OPTIMIZED_SP_EXECUTESQL no nível do banco de dados, use a seguinte instrução Transact-SQL:

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIMIZED_SP_EXECUTESQL = ON;

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

Observação

Este é um exemplo básico para sp_executesql. O exemplo não contém verificação de erros e não inclui verificações de regras de negócios, como garantir que os números de pedido não sejam duplicados entre tabelas.

CREATE PROCEDURE InsertSales @PrmOrderID INT,
    @PrmCustomerID INT,
    @PrmOrderDate DATETIME,
    @PrmDeliveryDate DATETIME
AS
DECLARE @InsertString AS NVARCHAR (500);
DECLARE @OrderMonth AS 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 a string construída dinamicamente, pois permite o uso de marcadores de parâmetro. Os marcadores de parâmetro tornam mais provável que o Mecanismo de Banco de Dados reutilize o plano de consulta gerado, o que ajuda a evitar compilações de consulta adicionais. Com EXECUTE, cada INSERT cadeia de caracteres é exclusiva porque os valores dos parâmetros são diferentes e seriam acrescentados ao final da cadeia de caracteres gerada dinamicamente. Quando executada, a consulta não seria parametrizada de uma forma que incentivasse a reutilização do plano e teria que ser compilada antes que cada INSERT instrução fosse executada, o que adicionaria uma entrada armazenada em cache separada da consulta no cache do plano.

C. Use 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. Duas SELECT instruções são executadas que usam o OUTPUT valor do parâmetro.

USE AdventureWorks2022;
GO

DECLARE @SQLString AS NVARCHAR (500);
DECLARE @ParmDefinition AS NVARCHAR (500);
DECLARE @SalesOrderNumber AS NVARCHAR (25);
DECLARE @IntVariable AS 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 inserido chamado @level.

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

Conteúdo relacionado