Compartilhar 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 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_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 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 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.

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 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 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 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 inserido chamado @level.

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

Conteúdo relacionado