sp_executesql (Transact-SQL)

Article
12/15/2008

Mis à jour : 5 décembre 2005

Exécute une instruction ou un lot d'instructions Transact-SQL, réutilisable plusieurs fois ou créé dynamiquement. L'instruction ou le lot d'instructions Transact-SQL peut contenir des paramètres incorporés.

Icône Lien de rubrique Conventions de syntaxe de Transact-SQL

Syntaxe

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

Arguments

[ @stmt = ] stmt
Chaîne Unicode contenant une instruction ou un lot d'instructions Transact-SQL. L'argument stmt doit être une constante Unicode ou une variable Unicode. L'utilisation d'expressions Unicode plus complexes (comme la concaténation de deux chaînes avec l'opérateur +) n'est pas autorisée. L'utilisation de constantes de caractères n'est pas autorisée. Si une constante Unicode est spécifiée, elle doit porter le préfixe N. Par exemple, la constante Unicode N'sp_who' est valide alors que la constante de type caractère 'sp_who' ne l'est pas. La taille de la chaîne n'est limitée que par la quantité de mémoire disponible sur le serveur de base de données. Sur les serveurs 64 bits, la taille de la chaîne est limitée à 2 Go, la taille maximale de nvarchar(max).

Remarque :

stmt peut contenir des paramètres possédant la même forme qu'un nom de variable, par exemple : N'SELECT * FROM HumanResources.Employee WHERE EmployeeID = @IDParameter'

Chaque paramètre inclus dans stmt doit posséder une entrée correspondante dans la liste des définitions de paramètres @params et dans la liste des valeurs de paramètres.

Remarque :
stmt peut contenir des paramètres possédant la même forme qu'un nom de variable, par exemple : `N'SELECT * FROM HumanResources.Employee WHERE EmployeeID = @IDParameter'`

[ @params = ] **N'@**parameter_namedata_type[ ,... n ] '
Chaîne contenant les définitions de tous les paramètres qui ont été incorporés dans stmt. Cette chaîne doit être une constante Unicode ou une variable Unicode. Chaque définition de paramètre se compose d'un nom de paramètre et d'un type de données. L'argument n correspond à un espace réservé pour d'autres définitions de paramètres. Chaque paramètre spécifié dans stmt doit être défini dans @params. Si l'instruction ou le lot d'instructions Transact-SQL figurant dans stmt ne contient aucun paramètre, il est inutile d'utiliser @params. La valeur par défaut de ce paramètre est NULL.

[ **@**param1 = ] 'value1'
Valeur du premier paramètre qui est défini dans la chaîne de paramètres. Cette valeur peut être une constante ou une variable Unicode. Il est nécessaire de fournir une valeur de paramètre pour chaque paramètre inclus dans stmt. Aucune valeur n'est requise si l'instruction ou le lot d'instructions Transact-SQL figurant dans stmt ne contient aucun paramètre.

[ OUT | OUTPUT ]
Indique que le paramètre est un paramètre de sortie. Les paramètres de type text, ntext et image peuvent être utilisés en tant que paramètres OUTPUT sauf si la procédure est une procédure CLR (Common Language Runtime). Un paramètre de sortie qui utilise le mot clé OUTPUT peut être un espace réservé de curseur, sauf si la procédure est une procédure CLR (Common Language Runtime).

n
Représente un espace réservé destiné aux valeurs de paramètres supplémentaires. Ces valeurs doivent être des constantes ou des variables. Leur degré de complexité ne doit pas dépasser celui d'expressions telles que les fonctions ou expressions créées à l'aide d'opérateurs.

Valeurs des codes de retour

0 (succès) ou autre que zéro (échec)

Notes

La procédure sp_executesql a le même comportement vis-à-vis des lots d'instructions, de l'étendue des noms et du contexte de base de données que l'instruction EXECUTE. L'instruction ou le lot d'instructions Transact-SQL figurant dans le paramètre stmt de sp_executesql n'est compilé qu'au moment de l'exécution de l'instruction sp_executesql. Le contenu de stmt est alors compilé et exécuté en tant que plan d'exécution distinct de celui du lot qui a appelé sp_executesql. Le lot sp_executesql ne peut pas faire référence à des variables déclarées dans le lot qui a appelé sp_executesql. Les curseurs ou les variables locaux du lot sp_executesql ne sont pas visibles pour le lot qui appelle sp_executesql. Les modifications apportées au contexte de base de données ne durent que jusqu'à la fin de l'exécution de l'instruction sp_executesql.

La procédure sp_executesql peut être utilisée à la place de procédures stockées afin d'exécuter une instruction Transact-SQL plusieurs fois lorsque la modification des valeurs de paramètres de l'instruction constitue l'unique changement. L'instruction Transact-SQL même demeurant constante, seules les valeurs de paramètre changent. Par conséquent, l'optimiseur de requête de SQL Server peut réutiliser le plan d'exécution généré pour la première exécution.

Remarque :
Pour améliorer les performances, utilisez des noms d'objets complets dans la chaîne d'instruction.

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

Les paramètres de sortie peuvent également être utilisés avec sp_executesql. L'exemple suivant récupère une fonction (professionnelle) dans la table AdventureWorks.HumanResources.Employee et la retourne dans le paramètre de sortie @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;

La possibilité de substitution de paramètres dans sp_executesql présente les avantages suivants lors de l'utilisation de l'instruction EXECUTE pour exécuter une chaîne :

Le texte de l'instruction Transact-SQL contenu dans la chaîne sp_executesql ne changeant pas entre les différentes exécutions, il est probable que l'optimiseur de requête calque dans ce cas l'instruction Transact-SQL de la deuxième exécution sur le plan d'exécution généré pour la première exécution. Cela évite donc à SQL Server de devoir compiler la 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.

Autorisations

Nécessite l'appartenance au rôle public en tant que membre.

Ensemble de résultats

Retourne les ensembles de résultats de toutes les instructions SQL de la chaîne SQL.

Exemples

A. Exécution d'une instruction SELECT simple

Cet exemple illustre la création et l'exécution d'une instruction SELECT simple contenant un paramètre incorporé appelé @level.

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

B. Exécution d'une chaîne créée dynamiquement

L'exemple suivant illustre l'utilisation de sp_executesql pour exécuter une chaîne créée dynamiquement. La procédure stockée proposée sert à l'insertion de données dans un ensemble de tables utilisées pour partitionner les données commerciales d'une année. Il existe une table par mois de l'année, d'après le format suivant :

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)
    )

Cet exemple de procédure stockée permet de créer et d'exécuter dynamiquement une instruction INSERT destinée à insérer les nouvelles commandes dans la table appropriée. L'exemple utilise la date de commande pour générer le nom de la table devant contenir les données, puis incorpore ce nom dans une instruction INSERT.

Remarque :
Il s'agit d'un exemple simple illustrant l'utilisation de sp_executesql. L'exemple ne prévoit pas de détection d'erreur et n'inclut aucun contrôle des règles commerciales, telles que la recherche de numéros de commande en double dans les différentes 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

Pour cette procédure, l'utilisation de sp_executesql est plus efficace que l'utilisation d'EXECUTE pour exécuter une chaîne. Si vous utilisez sp_executesql, seules 12 versions de la chaîne INSERT sont générées (une par table mensuelle). Avec EXECUTE, chaque chaîne INSERT est unique car les valeurs de paramètres diffèrent. Bien que ces deux méthodes génèrent le même nombre de lots d'instructions, la similitude des chaînes INSERT générées par sp_executesql renforce la probabilité de réutilisation des plans d'exécution par l'optimiseur de requête.

C. Utilisation du paramètre OUTPUT

L'exemple suivant utilise un paramètre OUTPUT pour stocker l'ensemble de résultats généré par l'instruction SELECT dans le paramètre @SQLString. Deux instructions SELECT qui utilisent la valeur du paramètre OUTPUT sont ensuite exécutées.

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;

Voir aussi

Historique des modifications

Version	Historique
5 décembre 2005	Nouveau contenu : Ajout de l'exemple C. Contenu modifié : Correction de la syntaxe du paramètre OUTPUT. Dans la définition de stmt et de parameter_name, l'expression « une variable qui peut être implicitement convertie en ntext » a été remplacée par « une variable Unicode ».

5 décembre 2005

Nouveau contenu :

Ajout de l'exemple C.

Contenu modifié :

Correction de la syntaxe du paramètre OUTPUT.
Dans la définition de stmt et de parameter_name, l'expression « une variable qui peut être implicitement convertie en ntext » a été remplacée par « une variable Unicode ».