Compartilhar via

  • Artigo

Usando sp_executesql

Para executar uma cadeia de caracteres, recomendamos que você use o procedimento armazenado sp_executesql em vez da instrução EXECUTE. Como esse procedimento armazenado dá suporte à substituição de parâmetro, o sp_executesql é mais versátil que EXECUTE; e como o sp_executesql gera planos de execução mais prováveis de serem reusados pelo SQL Server, o sp_executesql é mais eficiente que EXECUTE.

Lotes auto-suficientes

Quando o sp_executesql ou a instrução EXECUTE executa uma cadeia de caracteres, esta é executada como seu próprio lote auto-suficiente. O SQL Server compila a instrução Transact-SQL ou as instruções da cadeia de caracteres em um plano de execução que está separado do plano de execução do lote que contém o sp_executesql ou a instrução EXECUTE. As seguintes regras se aplicam a lotes auto-suficientes:

  • As instruções Transact-SQL no sp_executesql ou a cadeia de caracteres EXECUTE não são compiladas em um plano de execução até que sp_executesql ou a instrução EXECUTE seja executada. As cadeias de caracteres não são analisadas ou verificadas quanto a erros até que sejam executadas. Os nomes referenciados nas cadeias de caracteres não são resolvidos até serem executados.

  • As instruções Transact-SQL na cadeia de caracteres executada não têm acesso a nenhuma variável declarada no lote que contenha sp_executesql ou a instrução EXECUTE. O lote que contém sp_executesql ou a instrução EXECUTE não tem acesso a nenhuma variável ou cursor de local definido na cadeia de caracteres executada.

  • Se a cadeia de caracteres executada tiver uma instrução USE que altera o contexto do banco de dados, a alteração do contexto do banco de dados só durará até que sp_executesql ou a instrução EXECUTE seja executada.

A execução dos dois lotes seguintes ilustra esses pontos:

/*Show not having access to variables from the calling batch. */
DECLARE @CharVariable CHAR(3);
SET @CharVariable = 'abc';
/* sp_executesql fails because @CharVariable has gone out of scope. */
EXECUTE sp_executesql N'PRINT @CharVariable';
GO

/* Show database context resetting after sp_executesql finishes. */
USE master;
GO
EXECUTE sp_executesql N'USE AdventureWorks;'
GO
/* This statement fails because the database context
   has now returned to master. */
SELECT * FROM Sales.Store;
GO

Substituindo valores de parâmetro

O sp_executesql dá suporte à substituição de valores de quaisquer parâmetros especificados na cadeia de caracteres Transact-SQL, mas a instrução EXECUTE não. Portanto, as cadeias de caracteres Transact-SQL geradas pelo sp_executesql são mais semelhante àquelas geradas pela instrução EXECUTE. O otimizador de consulta SQL Server provavelmente corresponderá às instruções Transact-SQL de sp_executesql com planos de execução das instruções previamente executadas, economizando a sobrecarga de compilação de um novo plano de execução.

Com a instrução EXECUTE, todos os valores de parâmetro devem ser convertidos para caractere ou Unicode e fazer parte da cadeia de caracteres Transact-SQL.

Se a instrução for executada repetidamente, uma cadeia de caracteres Transact-SQL completamente nova deverá ser criada para cada execução, mesmo quando as únicas diferenças estiverem nos valores fornecidos aos parâmetros. Isso gera sobrecarga adicional das seguintes maneiras:

  • A habilidade do otimizador de consulta SQL Server para corresponder a nova cadeia de caracteres Transact-SQL com um plano de execução existente é reduzida pela alteração constante dos valores de parâmetro no texto da cadeia de caracteres, especialmente em instruções Transact-SQL complexas.

  • Toda a cadeia de caracteres deve ser recriada para cada execução.

  • Os valores de parâmetro (diferentes dos valores de caractere ou Unicode) devem ser convertidos em caractere ou formato Unicode para cada execução.

O sp_executesql dá suporte à configuração de valores de parâmetro separadamente da cadeia de caracteres Transact-SQL:

DECLARE @IntVariable INT;
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);

/* Build the SQL string one time. */
SET @SQLString =
     N'SELECT * FROM AdventureWorks.Sales.Store WHERE SalesPersonID = @SalesID';
/* Specify the parameter format one time. */
SET @ParmDefinition = N'@SalesID int';

/* Execute the string with the first parameter value. */
SET @IntVariable = 275;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @SalesID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 276;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @SalesID = @IntVariable;

O sp_executesql oferece os seguintes benefícios adicionais:

  • Como o texto real da instrução Transact-SQL não é alterado entre as execuções, o otimizador de consulta deve corresponder à 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.

    ObservaçãoObservação

    Os nomes de objeto na cadeia de caracteres da instrução devem ser totalmente qualificados para SQL Server para reutilizar o plano de execução.

Reutilizando planos de execução

Nas versões anteriores do SQL Server, o único modo de reutilizar planos de execução é definir as instruções Transact-SQL como um procedimento armazenado e fazer com que o aplicativo execute o procedimento armazenado. Isso gera sobrecarga administrativa adicional para os aplicativos. O uso do sp_executesql pode ajudar a reduzir essa sobrecarga e ainda deixar o SQL Server reutilizar os planos de execução. O sp_executesql pode ser usado em vez de procedimentos armazenados ao executar várias vezes a instrução Transact-SQL, quando a única variação está nos valores de parâmetro fornecidos à instrução Transact-SQL. Como as instruções Transact-SQL permanecem constantes e somente os valores de parâmetro são alterados, é provável que o otimizador de consulta SQL Server reutilize o plano de execução gerado para a primeira execução.

O exemplo a seguir cria e executa uma instrução DBCC CHECKDB para todo banco de dados em um servidor, com exceção dos quatro bancos de dados do sistema.

USE master;
GO
SET NOCOUNT ON;
GO
DECLARE AllDatabases CURSOR FOR
SELECT name FROM sys.databases WHERE database_id > 4
OPEN AllDatabases

DECLARE @DBNameVar NVARCHAR(128)
DECLARE @Statement NVARCHAR(300)

FETCH NEXT FROM AllDatabases INTO @DBNameVar
WHILE (@@FETCH_STATUS = 0)
BEGIN
   PRINT N'CHECKING DATABASE ' + @DBNameVar
   SET @Statement = N'USE ' + @DBNameVar + CHAR(13)
      + N'DBCC CHECKDB (' + @DBNameVar + N')' + N'WITH PHYSICAL_ONLY'
   EXEC sp_executesql @Statement
   PRINT CHAR(13) + CHAR(13)
   FETCH NEXT FROM AllDatabases INTO @DBNameVar
END

CLOSE AllDatabases
DEALLOCATE AllDatabases
GO
SET NOCOUNT OFF;
GO

O driver ODBC SQL Server usa o sp_executesql para implementar o SQLExecDirect quando a instrução Transact-SQL que está sendo executada contém marcadores de parâmetro associados. Isso amplia as vantagens fornecidas pelo sp_executesql a todos os aplicativos que usam ODBC ou APIs definidas sobre ODBC, como RDO. Os aplicativos de ODBC existentes transportados para o SQL Server adquirem automaticamente os ganhos de desempenho sem a necessidade de serem regravados. A única exceção é que o sp_executesql não é usado com parâmetros de dados em execução. Para obter mais informações, consulte Usando parâmetros de instrução.

O SQL Server Native Client ODBC Provider também usa o sp_executesql para implementar a execução direta de instruções com parâmetros associados. Os aplicativos que usam OLE DB ou ADO adquirem as vantagens fornecidas pelo sp_executesql sem a necessidade de serem regravadas.