Utilisation de sp_executesql

  • Article

Pour exécuter une chaîne, il est préférable d'utiliser la procédure stockée sp_executesql plutôt que l'instruction EXECUTE. En effet, non seulement la prise en charge de la substitution de paramètres rend la procédure stockée sp_executesql plus polyvalente que l'instruction EXECUTE, mais elle accroît également son efficacité par rapport à l'instruction EXECUTE grâce à la génération de plans d'exécution susceptibles d'être plus souvent réutilisés par SQL Server 2005.

Lots d'instructions indépendants

Quand une chaîne est exécutée à l'aide de sp_executesql ou de l'instruction EXECUTE, elle l'est en tant que lot d'instructions indépendant. SQL Server compile la ou les instructions Transact-SQL de la chaîne dans un plan d'exécution distinct du plan d'exécution du lot d'instructions contenant sp_executesql ou l'instruction EXECUTE. Les règles suivantes s'appliquent aux lots d'instructions indépendants :

  • Les instructions Transact-SQL de la chaîne sp_executesql ou EXECUTE ne sont compilées dans un plan d'exécution que lorsque la procédure sp_executesql ou l'instruction EXECUTE est exécutée. Aucune erreur n'est recherchée dans les chaînes tant qu'elles ne sont pas exécutées. Les noms référencés dans les chaînes ne sont pas résolus tant qu'elles ne sont pas exécutées.
  • Les instructions Transact-SQL de la chaîne exécutée n'ont pas accès aux variables déclarées dans le lot d'instructions contenant sp_executesql ou l'instruction EXECUTE. Le lot d'instructions contenant sp_executesql ou l'instruction EXECUTE n'a pas accès aux variables ou aux curseurs locaux définis dans la chaîne exécutée.
  • Si la chaîne exécutée comporte une instruction USE qui modifie le contexte de la base de données, cette modification ne dure que le temps de l'exécution de sp_executesql ou de l'instruction EXECUTE.

L'exécution des deux lots d'instructions suivants illustre ces points :

/*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

Substitution des valeurs de paramètres

Contrairement à l'instruction EXECUTE, la procédure sp_executesql prend en charge la substitution des valeurs de paramètres pour tout paramètre spécifié dans la chaîne Transact-SQL. Par conséquent, les chaînes Transact-SQL générées par sp_executesql offrent une plus grande similitude que celles générées par l'instruction EXECUTE. L'optimiseur de requête de SQL Server mettra probablement en correspondance les instructions Transact-SQL de sp_executesql avec les plans d'exécution des instructions exécutées précédemment, évitant ainsi de compiler un nouveau plan d'exécution.

Avec l'instruction EXECUTE, toutes les valeurs de paramètres doivent être converties en caractères ou en Unicode et appartenir à la chaîne Transact-SQL.

Si l'instruction est exécutée à plusieurs reprises, une nouvelle chaîne Transact-SQL doit être construite à chaque exécution, même lorsque les seules différences concernent les valeurs des paramètres. Pour différentes raisons, ceci provoque une surcharge supplémentaire :

  • La capacité de l'optimiseur de requête de SQL Server à mettre en correspondance la nouvelle chaîne Transact-SQL avec un plan d'exécution existant est gênée par les variations constantes des valeurs de paramètres dans le texte de la chaîne, en particulier dans les instructions Transact-SQL complexes.
  • La chaîne doit être totalement reconstruite à chaque exécution.
  • Les valeurs de paramètres (autre que les valeurs caractères ou Unicode) doivent avoir un format de type Unicode ou caractères à chaque exécution.

La procédure sp_executesql prend en charge la définition des valeurs de paramètres en dehors de la chaîne 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;

sp_executesql présente les avantages supplémentaires suivants :

  • Puisque le texte de l'instruction Transact-SQL ne change pas entre les exécutions, l'optimiseur de requête mettra probablement en correspondance l'instruction Transact-SQL de la deuxième exécution et le plan d'exécution généré pour la première exécution. SQL Server ne doit donc pas compiler une deuxième instruction.
  • La chaîne Transact-SQL est créée une seule fois.
  • Le paramètre de type entier est spécifié dans son format d'origine. La conversion en Unicode n'est pas nécessaire.
    ms175170.note(fr-fr,SQL.90).gifRemarque :
    Les noms d'objets apparaissant dans la chaîne de l'instruction doivent être qualifiés afin que SQL Server puisse réutiliser le plan d'exécution.

Réutilisation des plans d'exécution

Dans les versions antérieures de SQL Server, la seule manière de pouvoir réutiliser les plans d'exécution consistait à définir les instructions Transact-SQL en tant que procédures stockées, puis de les faire exécuter par l'application. Cette manière entraînait toutefois une surcharge administrative pour les applications. L'utilisation de la procédure sp_executesql contribue à réduire cette surcharge tout en permettant à SQL Server de réutiliser les plans d'exécution. sp_executesql peut être utilisée à la place des procédures stockées lorsqu'une instruction Transact-SQL est exécutée plusieurs fois avec, comme seule variation, une modification des valeurs de paramètres fournies à l'instruction Transact-SQL. Étant donné que les instructions Transact-SQL elles-mêmes demeurent constantes et que seules les valeurs de paramètres changent, l'optimiseur de requête de SQL Server peut réutiliser le plan d'exécution qu'il génère à la première exécution.

L'exemple suivant illustre la construction et l'exécution d'une instruction DBCC CHECKDB pour chaque base de données du serveur, à l'exception des quatre bases de données système.

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

Le pilote ODBC de SQL Server utilise sp_executesql pour implémenter SQLExecDirect lorsque l'instruction Transact-SQL en cours d'exécution contient des marqueurs de paramètres liés. Il s'ensuit une extension des avantages de la procédure sp_executesql à l'ensemble des applications qui recourent à ODBC ou aux API définies sur ODBC (RDO, par exemple). Les applications ODBC existantes portées vers SQL Server 2005 acquièrent automatiquement les nouvelles performances sans devoir être réécrites. Il existe toutefois une exception : sp_executesql n'est pas utilisée avec des paramètres de données en cours d'exécution. Pour plus d'informations, consultez Using Statement Parameters.

Le fournisseur OLE DB pour SQL Native Client utilise également sp_executesql pour implémenter l'exécution directe des instructions ayant des paramètres liés. Les applications utilisant OLE DB ou ADO bénéficient des avantages fournis par sp_executesql sans devoir être réécrites.

Voir aussi

Autres ressources

Injection SQL
DECLARE @local\_variable (Transact-SQL)
SELECT (Transact-SQL)
sp_executesql (Transact-SQL)

Aide et Informations

Assistance sur SQL Server 2005