Artigo
09/28/2010

sp_executesql (Transact-SQL)

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

Observação sobre segurança
Executar instruções Transact-SQL em tempo de compilação pode expor os aplicativos a ataques mal-intencionados, como injeção de SQL.

Ícone de vínculo de tópico Convenções de sintaxe Transact-SQL

Sintaxe

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

Argumentos

[ @statement = ] statement
É uma cadeia de caracteres Unicode que contém uma instrução ou lote um Transact-SQL. statement deve ser uma constante Unicode ou uma variável Unicode. Mais expressões Unicode complexas, como concatenar duas cadeias de caracteres com o operador +, não são permitidas. Constantes de caracteres não são permitidas. Se uma constante Unicode for especificada, ela deverá ser prefixada 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 de máximo de nvarchar(max).

Observação

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 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_namedata_type [ ,... n ] '
É uma cadeia de caracteres que contém as definições de todos os parâmetros incorporados em 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. Cada parâmetro especificado em statement deve ser definido em @params. Se a instrução ou o lote Transact-SQL em 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 cadeia de caracteres de 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 lote em stmt não tiver nenhum parâmetro.
[ OUT | OUTPUT ]
Indica que o parâmetro é um parâmetro de saída. Os parâmetros text, ntext e image podem ser usados como parâmetros OUTPUT, a menos que o procedimento seja CLR (Common Language Runtime). Um parâmetro de saída que usa a palavra-chave OUTPUT pode ser um espaço reservado de cursor, a menos que o procedimento seja CLR.
n
É um espaço reservado aos 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 construídas usando os operadores.

Observação
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'

Valores de código de retorno

0 (êxito) ou não zero (falha)

Conjuntos de resultados

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

Comentários

sp_executesql tem o mesmo comportamento que EXECUTE em relação a lotes, ao escopo de nomes e ao contexto de banco de dados. A instrução Transact-SQL ou o lote no parâmetro sp_executesql stmt não é compilado até que a instrução sp_executesql 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 lote sp_executesql não pode referenciar variáveis declaradas no lote que chama sp_executesql. Cursores locais ou variáveis no lote de 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 apenas até o fim da instrução sp_executesql.

sp_executesql pode ser usado no lugar 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.

Observação
Para melhorar o desempenho, use nomes de objeto totalmente qualificados na cadeia de caracteres de 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 int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

/* Build the SQL string one time.*/
SET @SQLString =
     N'SELECT EmployeeID, NationalIDNumber, Title, ManagerID
       FROM AdventureWorks.HumanResources.Employee 
       WHERE ManagerID = @ManagerID';
SET @ParmDefinition = N'@ManagerID tinyint';
/* Execute the string with the first parameter value. */
SET @IntVariable = 197;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @ManagerID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 109;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @ManagerID = @IntVariable;

Parâmetros de saída também podem ser usados com sp_executesql. O exemplo a seguir recupera um cargo da tabela AdventureWorks.HumanResources.Employee e o retorna no parâmetro de saída @max\_title.

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(Title) 
   FROM AdventureWorks.HumanResources.Employee
   WHERE ManagerID = @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;

A capacidade de substituir parâmetros em sp_executesql oferece as vantagens a seguir usando a 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 é alterado entre as execuções, o otimizador de consulta provavelmente fará a correspondência da 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 na função pública.

Exemplos

A. Executando uma instrução SELECT simples

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

EXECUTE sp_executesql 
          N'SELECT * FROM AdventureWorks.HumanResources.Employee 
          WHERE ManagerID = @level',
          N'@level tinyint',
          @level = 109;

B. Executando uma cadeia de caracteres dinamicamente construída

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 de um ano. Há uma tabela para cada mês do ano com 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 simples para sp_executesql. O exemplo não contém verificação de erros e não inclui verificações para regras de negócio, como garantir que os números do pedido não sejam duplicados nas 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 que usar EXECUTE para executar uma cadeia de caracteres. Quando sp_executesql é usado, há somente 12 versões da cadeia de caracteres INSERT que são geradas, uma para cada tabela mensal. Com EXECUTE, cada cadeia de caracteres INSERT é exclusiva porque os valores de parâmetro são diferentes. Embora os dois métodos gerem o mesmo número de lotes, a similaridade das cadeias de caracteres INSERT geradas por sp_executesql torna mais provável que o otimizador de consultas reutilize os planos de execução.

C. Usando o parâmetro OUTPUT

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

USE AdventureWorks;
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;

Para obter exemplos adicionais, consulte Usando sp_executesql.