Partilhar via

sp_executesql (Transact-SQL)

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada SQL do AzureAzure Synapse AnalyticsAnalytics Platform System (PDW)ponto de extremidade de análise SQL no Microsoft FabricWarehouse no Microsoft Fabric

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

Atenção

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

Transact-SQL convenções de sintaxe

Sintaxe

Sintaxe para SQL Server, Banco de Dados SQL do Azure, Instância Gerenciada 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 neste artigo usam o banco de dados de exemplo AdventureWorks2022 ou AdventureWorksDW2022, que você pode baixar da home page Microsoft SQL Server Samples and Community Projects.

Argumentos

Importante

Os argumentos para procedimentos armazenados estendidos devem ser inseridos na ordem específica, conforme descrito na seção de sintaxe. Se os parâmetros forem inseridos fora de ordem, ocorrerá uma mensagem de erro.

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

Uma cadeia de caracteres Unicode que contém uma instrução Transact-SQL ou lote. @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 N. Por exemplo, a constante Unicode N'sp_who' é válida, mas a constante de caractere 'sp_who' 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 máximo de nvarchar(max).

@stmt pode conter parâmetros com a mesma forma 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âmetros.

[ @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 Transact-SQL ou o lote em @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 no @stmt. Os valores não são necessários quando a instrução Transact-SQL ou o lote em @stmt não tem parâmetros.

{ SAÍDAS | SAÍDA }

Indica que o parâmetro é um parâmetro de saída. de texto , ntexte parâmetros de de imagem podem ser usados como parâmetros OUTPUT, a menos que o procedimento seja um procedimento CLR (Common Language Runtime). Um parâmetro de saída que usa a palavra-chave OUTPUT pode ser um espaço reservado para 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 de código de retorno

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

Conjunto de resultados

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

Comentários

sp_executesql parâmetros devem ser inseridos na ordem específica, conforme descrito na seção de 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 aos lotes, ao escopo dos nomes e ao contexto do banco de dados. A instrução Transact-SQL ou lote no parâmetro sp_executesql@stmt não é compilada até que a instrução sp_executesql seja executada. O conteúdo do @stmt é então compilado e executado como um plano de execução separado do plano de execução do lote chamado sp_executesql. O lote sp_executesql não pode fazer referência a variáveis declaradas no lote que chama sp_executesql. Os cursores ou variáveis locais no lote de sp_executesql não são visíveis para o lote que chama sp_executesql. As alterações no contexto do banco de dados duram apenas 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 dos parâmetros para a instrução é a única variação. Como a instrução Transact-SQL em si permanece constante e apenas 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 da instrução.

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 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 título de trabalho da tabela HumanResources.Employee no banco de dados de exemplo AdventureWorks2022 e o retorna no parâmetro de saída @max_title.

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 em relação ao uso da instrução EXECUTE para executar uma cadeia de caracteres:

  • Como o texto real da instrução Transact-SQL na cadeia de caracteres sp_executesql 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 Transact-SQL é construída apenas uma vez.

  • O parâmetro integer é especificado em seu formato nativo. A transmissão para Unicode não é necessária.

OPTIMIZED_SP_EXECUTESQL

Aplica-se a: Banco de Dados SQL do Azure

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

Quando os lotes são idênticos (excluindo quaisquer diferenças de parâmetro), a opção OPTIMIZED_SP_EXECUTESQL 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 invocarem 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 do sp_executesql compila e insere seu plano compilado no cache do plano. Outras sessões abortam a espera no bloqueio de compilação e reutilizam o plano assim que ele estiver disponível.

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

Observação

Antes de habilitar a configuração de escopo do banco de dados OPTIMIZED_SP_EXECUTESQL, 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 com escopo de banco de dados ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY. Habilitar essas duas opções pode reduzir significativamente a probabilidade de que problemas de desempenho relacionados a longos tempos de compilação, juntamente com bloqueios exclusivos excessivos do gerenciador de bloqueio (LCK_M_X) e esperas WAIT_ON_SYNC_STATISTICS_REFRESH.

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 a participação na função pública.

Exemplos

Um. Executar uma instrução SELECT

O exemplo a seguir cria e executa uma instrução SELECT 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 criada dinamicamente. O exemplo de procedimento armazenado é usado para inserir dados em um conjunto de tabelas que são usadas para particionar dados de vendas por 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 exemplo de procedimento armazenado cria e executa dinamicamente uma instrução INSERT para inserir novas ordens na tabela correta. O exemplo usa a data da ordem para criar o nome da tabela que deve conter os dados e, em seguida, incorpora esse 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 ordem 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 cadeia de caracteres construída dinamicamente, porque 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 cadeia de INSERT é 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 forma a incentivar a reutilização do plano e teria que ser compilada antes que cada instrução INSERT fosse executada, o que adicionaria uma entrada em cache separada da consulta no cache do plano.

C. Use o parâmetro OUTPUT

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

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 and Analytics Platform System (PDW)

D. Executar uma instrução SELECT

O exemplo a seguir cria e executa uma instrução SELECT 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