sp_executesql (Transact-SQL)

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Point de terminaison analytique SQL dans Microsoft FabricEntrepôt dans Microsoft Fabric

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

Conventions de la syntaxe Transact-SQL

Syntaxe

-- Syntax for SQL Server, Azure SQL Database, Azure Synapse Analytics, Parallel Data Warehouse  
  
sp_executesql [ @stmt = ] statement  
[   
  { , [ @params = ] N'@parameter_name data_type [ OUT | OUTPUT ][ ,...n ]' }   
     { , [ @param1 = ] 'value1' [ ,...n ] }  
]  

Arguments

[ @stmt= ] statement
Une chaîne Unicode qui contient une instruction Transact-SQL ou un lot. @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).

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.

[ @params= ] N’@parameter_namedata_type [ ,... n ] ’
Chaîne qui contient les définitions de tous les paramètres incorporés dans @stmt. La 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. 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 Transact-SQL ou le lot 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. Une valeur de paramètre doit être fournie pour chaque paramètre inclus dans @stmt. Les valeurs ne sont pas obligatoires lorsque l’instruction Transact-SQL ou le lot dans @stmt n’a aucun paramètre.

[ OUT | OUTPUT ]
Indique que le paramètre est un paramètre de sortie. Les paramètres de typetext, 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.

Codet de retour

0 (réussite) ou autre que zéro (échec)

Jeux de résultats

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

Notes

Les paramètres sp_executesql doivent être entrés dans l’ordre spécifique comme décrit dans la section « Syntaxe » précédemment dans cette rubrique. Si les paramètres sont entrés dans le désordre, un message d'erreur se produira.

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. 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 traitement sp_executesql ne peut pas faire référence à des variables déclarées dans le traitement qui a appelé sp_executesql. Les curseurs ou les variables locaux du traitement sp_executesql ne sont pas visibles pour le traitement 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.

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

Les paramètres de sortie peuvent également être utilisés avec sp_executesql. L’exemple suivant récupère un titre de travail à partir de la HumanResources.Employee table de l’exemple AdventureWorks2022 de base de données et le retourne dans le paramètre @max_titlede sortie.

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;  

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.

Exemples

R. 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 AdventureWorks2022.HumanResources.Employee   
          WHERE BusinessEntityID = @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.

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

C. Utilisation du paramètre OUTPUT

L’exemple suivant utilise un paramètre OUTPUT pour stocker le jeu 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 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;  

Exemples : Azure Synapse Analytics et Analytics Platform System (PDW)

D. 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.

-- Uses AdventureWorks2022
  
EXECUTE sp_executesql   
          N'SELECT * FROM AdventureWorksPDW2012.dbo.DimEmployee   
          WHERE EmployeeKey = @level',  
          N'@level TINYINT',  
          @level = 109;  

Voir aussi

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