sp_executesql (Transact-SQL)
Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsPDW (Analytics Platform System)Ponto de extremidade de SQL no Microsoft FabricWarehouse no Microsoft Fabric
Executa uma instrução ou lote Transact-SQL que pode ser reutilizado muitas vezes ou que é criado dinamicamente. A instrução ou lote do Transact-SQL pode conter parâmetros inseridos.
Cuidado
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_executesql
o . 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 SQL do Azure, Azure Synapse Analytics e Analytics Platform System (PDW).
sp_executesql [ @stmt = ] N'statement'
[
[ , [ @params = ] N'@parameter_name data_type [ { OUT | OUTPUT } ] [ , ...n ]' ]
[ , [ @param1 = ] 'value1' [ , ...n ] ]
]
Os exemplos de código Transact-SQL neste artigo usam o AdventureWorks2022
banco de dados de exemplo, que você pode baixar da home page Microsoft SQL Server Samples and Community Projects .
Argumentos
@stmt [ = ] N'declaração'
Uma cadeia de caracteres Unicode que contém uma instrução ou lote Transact-SQL. @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
arquivo . Por exemplo, a constante N'sp_who'
Unicode é 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 o mesmo formato 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âmetro.
@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 ou 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 [ = ] '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 em @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.
{ SAÍDA | 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
do 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 do código de retorno
0
(sucesso) ou diferente de zero (fracasso).
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
parâmetros devem ser inseridos na ordem específica, conforme descrito na seção 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 a lotes, o escopo de nomes e o contexto do banco de dados. A instrução Transact-SQL ou o lote no sp_executesql
parâmetro @stmt não é compilado até que a sp_executesql
instrução 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 sp_executesql
lote não pode fazer referência a variáveis declaradas no lote que chama sp_executesql
. Os cursores ou variáveis locais no lote 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 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
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 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 . O exemplo a seguir recupera um título de trabalho da tabela no AdventureWorks2022
banco de dados de HumanResources.Employee
exemplo e o retorna no parâmetro @max_title
de 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 cadeia de caracteres:
Como o texto real da instrução Transact-SQL na
sp_executesql
cadeia de caracteres 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 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 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 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 . 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 pedidos 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 uma cadeia de caracteres. Quando sp_executesql
é usado, há apenas 12 versões da cadeia de INSERT
caracteres que são geradas, uma para cada tabela mensal. Com EXECUTE
o , cada INSERT
cadeia de caracteres é exclusiva porque os valores de parâmetro são diferentes. Embora ambos os métodos gerem o mesmo número de lotes, a semelhança das cadeias de caracteres geradas por sp_executesql
torna mais provável que o otimizador de INSERT
consulta reutilize planos de execução.
C. Usar 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. Em seguida, são executadas duas SELECT
instruções 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 incorporado chamado @level
.
EXECUTE sp_executesql
N'SELECT * FROM AdventureWorksPDW2012.dbo.DimEmployee
WHERE EmployeeKey = @level',
N'@level TINYINT',
@level = 109;
Conteúdo relacionado
Comentários
https://aka.ms/ContentUserFeedback.
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulteEnviar e exibir comentários de