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

S’APPLIQUE À : ouiSQL Server (à partir de 2008) ouiAzure SQL Database ouiAzure SQL Data Warehouse ouiParallel Data Warehouse APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Exécute un lot ou une instruction Transact-SQLTransact-SQL, réutilisable plusieurs fois ou créé dynamiquement.Executes a Transact-SQLTransact-SQL statement or batch that can be reused many times, or one that has been built dynamically. L'instruction ou le traitement d'instructions Transact-SQLTransact-SQL peut contenir des paramètres incorporés.The Transact-SQLTransact-SQL statement or batch can contain embedded parameters.

Important

Les instructions Transact-SQLTransact-SQL compilées à l'exécution peuvent exposer les applications à des attaques malveillantes.Run time-compiled Transact-SQLTransact-SQL statements can expose applications to malicious attacks.

Icône de lien de rubrique Conventions de la syntaxe Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SyntaxeSyntax

-- 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 ] }  
]  

ArgumentsArguments

[ @stmt= ] statement[ @stmt= ] statement
Est une chaîne Unicode contenant un Transact-SQLTransact-SQL instruction ou le lot.Is a Unicode string that contains a Transact-SQLTransact-SQL statement or batch. @stmt doit être une constante Unicode ou une variable Unicode.@stmt must be either a Unicode constant or a Unicode variable. L'utilisation d'expressions Unicode plus complexes (comme la concaténation de deux chaînes avec l'opérateur +) n'est pas autorisée.More complex Unicode expressions, such as concatenating two strings with the + operator, are not allowed. L'utilisation de constantes de caractères n'est pas autorisée.Character constants are not allowed. Si une constante Unicode est spécifiée, elle doit porter le préfixe avec un N. Par exemple, la constante Unicode ne sp_who' est valide, mais la constante caractère 'sp_who' n’est pas.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. 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.The size of the string is limited only by available database server memory. Sur les serveurs 64 bits, la taille de la chaîne est limitée à 2 Go, la taille maximale de nvarchar (max).On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max).

Notes

@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'@stmt can contain parameters having the same form as a variable name, for example: N'SELECT * FROM HumanResources.Employee WHERE EmployeeID = @IDParameter'

Chaque paramètre inclus dans @stmt doit posséder une entrée correspondante à la fois dans le @liste des définitions de paramètre params et le paramètre de liste de valeurs.Each parameter included in @stmt must have a corresponding entry in both the @params parameter definition list and the parameter values list.

[ @params =] N'@nom_paramètre ** data_type [,... n ] '[ @params= ] N'@parameter_name**data_type [ ,... n ] '
Est une chaîne qui contient 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.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. Chaque définition de paramètre se compose d'un nom de paramètre et d'un type de données.Each parameter definition consists of a parameter name and a data type. n est un espace réservé qui indique les définitions de paramètres supplémentaires.n is a placeholder that indicates additional parameter definitions. Chaque paramètre spécifié dans @stmt doit être défini dans @params.Every parameter specified in @stmt must be defined in @params. Si le Transact-SQLTransact-SQL instruction ou le lot de @stmt ne contient-elle pas de paramètres, @params n’est pas obligatoire.If the Transact-SQLTransact-SQL statement or batch in @stmt does not contain parameters, @params is not required. La valeur par défaut de ce paramètre est NULL.The default value for this parameter is NULL.

[ @param1= ] 'value1'[ @param1= ] 'value1'
Valeur du premier paramètre qui est défini dans la chaîne de paramètres.Is a value for the first parameter that is defined in the parameter string. Cette valeur peut être une constante ou une variable Unicode.The value can be a Unicode constant or a Unicode variable. Il doit y avoir une valeur de paramètre fournie pour chaque paramètre inclus dans @stmt. Les valeurs ne sont pas requis lorsque la Transact-SQLTransact-SQL instruction ou le lot de @stmt n’a aucun paramètre.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 ]
Indique que le paramètre est un paramètre de sortie.Indicates that the parameter is an output parameter. texte, ntext, et image paramètres peuvent être utilisés comme paramètres de sortie, sauf si la procédure est une procédure du 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. 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).An output parameter that uses the OUTPUT keyword can be a cursor placeholder, unless the procedure is a CLR procedure.

nn
Représente un espace réservé destiné aux valeurs de paramètres supplémentaires.Is a placeholder for the values of additional parameters. Ces valeurs doivent être des constantes ou des variables.Values can only be constants or 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.Values cannot be more complex expressions such as functions, or expressions built by using operators.

Valeurs des codes de retourReturn Code Values

0 (réussite) ou autre que zéro (échec)0 (success) or non-zero (failure)

Jeux de résultatsResult Sets

Retourne les jeux de résultats de toutes les instructions SQL de la chaîne SQL.Returns the result sets from all the SQL statements built into the SQL string.

NotesRemarks

paramètres de sp_executesql doivent être entrées dans l’ordre spécifique, comme décrit dans la section « Syntaxe » précédemment dans cette rubrique.sp_executesql parameters must be entered in the specific order as described in the "Syntax" section earlier in this topic. Si les paramètres sont entrés dans le désordre, un message d'erreur se produira.If the parameters are entered out of order, an error message will occur.

La procédure sp_executesql a le même comportement vis-à-vis des traitements d'instructions, de l'étendue des noms et du contexte de base de données que l'instruction EXECUTE.sp_executesql has the same behavior as EXECUTE with regard to batches, the scope of names, and database context. Le Transact-SQLTransact-SQL instruction ou le lot de sp_executesql @stmt paramètre n’est pas compilé jusqu'à l’exécution de l’instruction sp_executesql.The Transact-SQLTransact-SQL statement or batch in the sp_executesql @stmt parameter is not compiled until the sp_executesql statement is executed. Le contenu de @stmt est alors compilé et exécuté en tant qu’un plan d’exécution distinct du plan d’exécution du lot qui a appelé 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. Le traitement sp_executesql ne peut pas faire référence à des variables déclarées dans le traitement qui a appelé sp_executesql.The sp_executesql batch cannot reference variables declared in the batch that calls sp_executesql. Les curseurs ou les variables locaux du traitement sp_executesql ne sont pas visibles pour le traitement qui appelle sp_executesql.Local cursors or variables in the sp_executesql batch are not visible to the batch that calls 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.Changes in database context last only to the end of the sp_executesql statement.

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

Notes

Pour améliorer les performances, utilisez des noms d'objets complets dans la chaîne d'instruction.To improve performance use fully qualified object names in the statement string.

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

Les paramètres de sortie peuvent également être utilisés avec sp_executesql.Output parameters can also be used with sp_executesql. L'exemple suivant récupère un poste dans la table AdventureWorks2012.HumanResources.Employee et le retourne dans le paramètre de sortie @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;  

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

  • Le texte de l'instruction Transact-SQLTransact-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-SQLTransact-SQL de la deuxième exécution sur le plan d'exécution généré pour la première exécution.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. Cela évite donc à SQL ServerSQL Server de devoir compiler la deuxième instruction.Therefore, SQL ServerSQL Server does not have to compile the second statement.

  • La chaîne Transact-SQLTransact-SQL est créée une seule fois.The Transact-SQLTransact-SQL string is built only one time.

  • Le paramètre de type entier est spécifié dans son format d'origine.The integer parameter is specified in its native format. La conversion en Unicode n'est pas nécessaire.Casting to Unicode is not required.

AutorisationsPermissions

Nécessite l'appartenance au rôle public.Requires membership in the public role.

ExemplesExamples

A.A. Exécution d'une instruction SELECT simpleExecuting a simple SELECT statement

Cet exemple illustre la création et l'exécution d'une instruction SELECT simple contenant un paramètre incorporé appelé @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. Exécution d'une chaîne créée dynamiquementExecuting a dynamically built string

L'exemple suivant illustre l'utilisation de sp_executesql pour exécuter une chaîne créée dynamiquement.The following example shows using sp_executesql to execute a dynamically built string. 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.The example stored procedure is used to insert data into a set of tables that are used to partition sales data for a year. Il existe une table par mois de l'année, d'après le format suivant :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)  
    )  

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.This sample stored procedure dynamically builds and executes an INSERT statement to insert new orders into the correct table. 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.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.

Notes

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

Pour cette procédure, l'utilisation de sp_executesql est plus efficace que l'utilisation d'EXECUTE pour exécuter une chaîne.Using sp_executesql in this procedure is more efficient than using EXECUTE to execute a string. Si vous utilisez sp_executesql, seules 12 versions de la chaîne INSERT sont générées (une par table mensuelle).When sp_executesql is used, there are only 12 versions of the INSERT string that are generated, one for each monthly table. Avec EXECUTE, chaque chaîne INSERT est unique car les valeurs de paramètres diffèrent.With EXECUTE, each INSERT string is unique because the parameter values are different. Bien que ces deux méthodes génèrent le même nombre de traitements 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.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. Utilisation du paramètre OUTPUTUsing the OUTPUT Parameter

L’exemple suivant utilise un OUTPUT paramètre permettant de stocker le jeu de résultats généré par le SELECT instruction dans le @SQLString paramètre. Deux SELECT instructions sont ensuite exécutées qui utilisent la valeur de la OUTPUT paramètre.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;  

Exemples : Azure SQL Data WarehouseAzure SQL Data Warehouse et Parallel Data WarehouseParallel Data WarehouseExamples: Azure SQL Data WarehouseAzure SQL Data Warehouse and Parallel Data WarehouseParallel Data Warehouse

D.D. Exécution d'une instruction SELECT simpleExecuting a simple SELECT statement

Cet exemple illustre la création et l'exécution d'une instruction SELECT simple contenant un paramètre incorporé appelé @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;  

Pour obtenir des exemples supplémentaires, consultez sp_executesql (Transact-SQL).For additional examples, see sp_executesql (Transact-SQL).

Voir aussiSee Also

EXECUTE (Transact-SQL) EXECUTE (Transact-SQL)
Procédures stockées système (Transact-SQL)System Stored Procedures (Transact-SQL)