sp_executesql (Transact-SQL)sp_executesql (Transact-SQL)

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure simAzure Synapse Analytics (SQL DW) simParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

Executa uma instrução ou lote Transact-SQLTransact-SQL que pode ser reutilizado muitas vezes ou que foi criado dinamicamente.Executes a Transact-SQLTransact-SQL statement or batch that can be reused many times, or one that has been built dynamically. A instrução ou lote do Transact-SQLTransact-SQL pode conter parâmetros inseridos.The Transact-SQLTransact-SQL statement or batch can contain embedded parameters.

Importante

Executar instruções Transact-SQLTransact-SQL em tempo de compilação pode expor os aplicativos a ataques maliciosos.Run time-compiled Transact-SQLTransact-SQL statements can expose applications to malicious attacks.

Ícone de link do tópico Convenções da sintaxe Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintaxeSyntax

-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse  
  
sp_executesql [ @stmt = ] statement  
[   
  { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }   
     { , [ @param1 = ] 'value1' [ ,...n ] }  
]  

ArgumentosArguments

[ @stmt =] instrução[ @stmt= ] statement
É uma cadeia de caracteres Unicode que Transact-SQLTransact-SQL contém uma instrução ou um lote.Is a Unicode string that contains a Transact-SQLTransact-SQL statement or batch. @stmt deve ser uma constante Unicode ou uma variável Unicode.@stmt must be either a Unicode constant or a Unicode variable. Mais expressões Unicode complexas, como concatenar duas cadeias de caracteres com o operador +, não são permitidas.More complex Unicode expressions, such as concatenating two strings with the + operator, are not allowed. Constantes de caracteres não são permitidas.Character constants are not allowed. 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 é.If a Unicode constant is specified, it must be prefixed with an N. For example, the Unicode constant N'sp_who' is valid, but the character constant 'sp_who' is not. O tamanho da cadeia de caracteres é limitado apenas pela memória disponível do servidor de banco de dados.The size of the string is limited only by available database server memory. Em servidores de 64 bits, o tamanho da cadeia de caracteres é limitado a 2 GB, o tamanho máximo de nvarchar (max).On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of 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'@stmt can contain parameters having the same form as a variable name, for example: 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.Each parameter included in @stmt must have a corresponding entry in both the @params parameter definition list and the parameter values list.

[ @params =] N '@parameter_name data_type [,... n ] '[ @params= ] N'@parameter_name data_type [ ,... n ] '
É uma cadeia de caracteres que contém as definições de todos os parâmetros que foram @inseridos em stmt. A cadeia de caracteres deve ser uma constante Unicode ou uma variável Unicode.Is one string that contains the definitions of all parameters that have been embedded in @stmt. The string must be either a Unicode constant or a Unicode variable. Cada definição de parâmetro consiste em um nome de parâmetro e um tipo de dados.Each parameter definition consists of a parameter name and a data type. n é um espaço reservado que indica definições de parâmetros adicionais.n is a placeholder that indicates additional parameter definitions. Todos os parâmetros especificados @em stmt devem ser definidos @em params.Every parameter specified in @stmt must be defined in @params. Se a Transact-SQLTransact-SQL instrução ou o lote @em stmt não contiver parâmetros @, params não será necessário.If the Transact-SQLTransact-SQL statement or batch in @stmt does not contain parameters, @params is not required. O valor padrão para este parâmetro é NULL.The default value for this parameter is NULL.

[ @param1 =] 'value1'[ @param1= ] 'value1'
É um valor para o primeiro parâmetro definido na cadeia de caracteres de parâmetro.Is a value for the first parameter that is defined in the parameter string. O valor pode ser uma constante Unicode ou uma variável Unicode.The value can be a Unicode constant or a Unicode variable. 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 Transact-SQLTransact-SQL instrução ou o lote @em stmt não tem parâmetros.There must be a parameter value supplied for every parameter included in @stmt. The values are not required when the Transact-SQLTransact-SQL statement or batch in @stmt has no parameters.

[ OUT | OUTPUT ][ OUT | OUTPUT ]
Indica que o parâmetro é um parâmetro de saída.Indicates that the parameter is an output parameter. os parâmetros Text, ntexte Image podem ser usados como parâmetros de saída, a menos que o procedimento seja um procedimento Common Language Runtime (CLR).text, ntext, and image parameters can be used as OUTPUT parameters, unless the procedure is a common language runtime (CLR) procedure. 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.An output parameter that uses the OUTPUT keyword can be a cursor placeholder, unless the procedure is a CLR procedure.

nn
É um espaço reservado aos valores de parâmetros adicionais.Is a placeholder for the values of additional parameters. Os valores só podem ser constantes ou variáveis.Values can only be constants or variables. Os valores não podem ser expressões mais complexas, como funções ou expressões construídas usando os operadores.Values cannot be more complex expressions such as functions, or expressions built by using operators.

Valores do código de retornoReturn Code Values

0 (êxito) ou não zero (falha)0 (success) or non-zero (failure)

Conjuntos de resultadosResult Sets

Retorna os conjuntos de resultados de todas as instruções SQL construídas na cadeia de caracteres SQL.Returns the result sets from all the SQL statements built into the SQL string.

ComentáriosRemarks

sp_executesql parâmetros devem ser inseridos na ordem específica, conforme descrito na seção "sintaxe" anteriormente neste tópico.sp_executesql parameters must be entered in the specific order as described in the "Syntax" section earlier in this topic. Se os parâmetros forem inseridos na ordem incorreta, uma mensagem de erro será exibida.If the parameters are entered out of order, an error message will occur.

sp_executesql tem o mesmo comportamento que EXECUTE em relação a lotes, ao escopo de nomes e ao contexto de banco de dados.sp_executesql has the same behavior as EXECUTE with regard to batches, the scope of names, and database context. A Transact-SQLTransact-SQL instrução ou o lote no parâmetro @sp_executesql stmt não é compilado até que a instrução sp_executesql seja executada.The Transact-SQLTransact-SQL statement or batch in the sp_executesql @stmt parameter is not compiled until the sp_executesql statement is executed. 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.The contents of @stmt are then compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql. O lote sp_executesql não pode referenciar variáveis declaradas no lote que chama sp_executesql.The sp_executesql batch cannot reference variables declared in the batch that calls 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.Local cursors or variables in the sp_executesql batch are not visible to the batch that calls sp_executesql. As alterações no contexto de banco de dados duram apenas até o fim da instrução sp_executesql.Changes in database context last only to the end of the sp_executesql statement.

sp_executesql poderá ser usado no lugar de procedimentos armazenados para executar uma instrução Transact-SQLTransact-SQL muitas vezes quando a alteração nos valores de parâmetro para a instrução for a única variação.sp_executesql can be used instead of stored procedures to execute a Transact-SQLTransact-SQL statement many times when the change in parameter values to the statement is the only variation. Como a instrução Transact-SQLTransact-SQL em si permanece constante e somente os valores de parâmetro são alterados, é provável que o otimizador de consulta do SQL ServerSQL Server reutilize o plano de execução gerado para a primeira execução.Because the Transact-SQLTransact-SQL statement itself remains constant and only the parameter values change, the SQL ServerSQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.

Observação

Para melhorar o desempenho, use nomes de objeto totalmente qualificados na cadeia de caracteres de instrução.To improve performance use fully qualified object names in the statement string.

sp_executesql oferece suporte à configuração de valores de parâmetro separadamente da cadeia de caracteres Transact-SQLTransact-SQL, conforme mostrado no exemplo a seguir.sp_executesql supports the setting of parameter values separately from the Transact-SQLTransact-SQL string as shown in the following example.

DECLARE @IntVariable int;  
DECLARE @SQLString nvarchar(500);  
DECLARE @ParmDefinition nvarchar(500);  
  
/* Build the SQL string one time.*/  
SET @SQLString =  
     N'SELECT BusinessEntityID, NationalIDNumber, JobTitle, LoginID  
       FROM AdventureWorks2012.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;  

Parâmetros de saída também podem ser usados com sp_executesql.Output parameters can also be used with sp_executesql. O exemplo a seguir recupera um cargo da tabela AdventureWorks2012.HumanResources.Employee e o retorna no parâmetro de saída @max_title.The following example retrieves a job title from the AdventureWorks2012.HumanResources.Employee table and returns it in the output parameter @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(JobTitle)   
   FROM AdventureWorks2012.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;  

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:Being able to substitute parameters in sp_executesql offers the following advantages to using the EXECUTE statement to execute a string:

  • Como o texto real da instrução Transact-SQLTransact-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-SQLTransact-SQL na segunda execução com o plano de execução gerado para a primeira execução.Because the actual text of the Transact-SQLTransact-SQL statement in the sp_executesql string does not change between executions, the query optimizer will probably match the Transact-SQLTransact-SQL statement in the second execution with the execution plan generated for the first execution. Portanto, o SQL ServerSQL Server não precisa compilar a segunda instrução.Therefore, SQL ServerSQL Server does not have to compile the second statement.

  • A cadeia de caracteres Transact-SQLTransact-SQL é criada apenas uma vez.The Transact-SQLTransact-SQL string is built only one time.

  • O parâmetro numérico inteiro é especificado em seu formato nativo.The integer parameter is specified in its native format. A conversão para Unicode não é necessária.Casting to Unicode is not required.

PermissõesPermissions

Requer associação à função public.Requires membership in the public role.

ExemplosExamples

a.A. Executando uma instrução SELECT simplesExecuting a simple SELECT statement

O exemplo a seguir cria e executa uma instrução SELECT simples que contém um parâmetro inserido chamado @level.The following example creates and executes a simple SELECT statement that contains an embedded parameter named @level.

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

B.B. Executando uma cadeia de caracteres dinamicamente construídaExecuting a dynamically built string

O exemplo a seguir mostra o uso de sp_executesql para executar uma cadeia de caracteres dinamicamente construída.The following example shows using sp_executesql to execute a dynamically built string. 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.The example stored procedure is used to insert data into a set of tables that are used to partition sales data for a year. Há uma tabela para cada mês do ano com o seguinte formato:There is one table for each month of the year that has the following format:

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.This sample stored procedure dynamically builds and executes an INSERT statement to insert new orders into the correct table. 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.The example uses the order date to build the name of the table that should contain the data, and then incorporates that name into an INSERT statement.

Observação

Este é um exemplo simples para sp_executesql.This is a simple example for sp_executesql. O exemplo não contém verificação de erros e não inclui verificações para regras de negócios, como garantir que os números do pedido não sejam duplicados nas tabelas.The example does not contain error checking and does not include checks for business rules, such as guaranteeing that order numbers are not duplicated between tables.

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.Using sp_executesql in this procedure is more efficient than using EXECUTE to execute a string. Quando sp_executesql é usado, há somente 12 versões da cadeia de caracteres INSERT que são geradas, uma para cada tabela mensal.When sp_executesql is used, there are only 12 versions of the INSERT string that are generated, one for each monthly table. Com EXECUTE, cada cadeia de caracteres INSERT é exclusiva porque os valores de parâmetro são diferentes.With EXECUTE, each INSERT string is unique because the parameter values are different. 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.Although both methods generate the same number of batches, the similarity of the INSERT strings generated by sp_executesql makes it more likely that the query optimizer will reuse execution plans.

C.C. Usando o parâmetro OUTPUTUsing the OUTPUT Parameter

O exemplo a seguir usa OUTPUT um parâmetro para armazenar o conjunto de resultados gerado SELECT pela instrução no @SQLString parâmetro. Duas SELECT instruções são executadas e usam o valor do OUTPUT parâmetro.The following example uses an OUTPUT parameter to store the result set generated by the SELECT statement in the @SQLString parameter.Two SELECT statements are then executed that use the value of the OUTPUT parameter.

USE AdventureWorks2012;  
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 (SQL DW)Azure Synapse Analytics (SQL DW) e Parallel Data WarehouseParallel Data WarehouseExamples: Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW) and Parallel Data WarehouseParallel Data Warehouse

D.D. Executando uma instrução SELECT simplesExecuting a simple SELECT statement

O exemplo a seguir cria e executa uma instrução SELECT simples que contém um parâmetro inserido chamado @level.The following example creates and executes a simple SELECT statement that contains an embedded parameter named @level.

-- Uses AdventureWorks  
  
EXECUTE sp_executesql   
          N'SELECT * FROM AdventureWorksPDW2012.dbo.DimEmployee   
          WHERE EmployeeKey = @level',  
          N'@level tinyint',  
          @level = 109;  

Consulte TambémSee Also

EXECUTE (Transact-SQL) EXECUTE (Transact-SQL)
Procedimentos armazenados do sistema (Transact-SQL)System Stored Procedures (Transact-SQL)