sp_executesql (Transact-SQL)

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

Exécute une instruction Transact-SQL ou un lot qui peut être réutilisé plusieurs fois, ou une instruction générée dynamiquement. L’instruction ou le traitement d’instructions Transact-SQL peut contenir des paramètres incorporés.

Attention

Les instructions Transact-SQL compilées au runtime peuvent exposer des applications à des attaques malveillantes. Vous devez paramétrer vos requêtes lors de l’utilisation sp_executesql. Pour plus d’informations, consultez Injection SQL.

Conventions de la syntaxe Transact-SQL

Syntaxe

Syntaxe pour SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics et Système de plateforme d’analyse (PDW).

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

Les exemples de code Transact-SQL de cet article sont fondés sur l’échantillon de base de données AdventureWorks2022 ou AdventureWorksDW2022 fourni, que vous pouvez télécharger à partir de la page d’accueil Échantillons et projets communautaires Microsoft SQL Server.

Arguments

[ @stmt = ] N’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. Les constantes de caractères ne sont pas autorisées. Les constantes Unicode doivent être précédées d’un N. Par exemple, la constante Unicode N'sp_who' est valide, mais la constante de caractère 'sp_who' n’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).

@stmt peut contenir des paramètres ayant le même formulaire 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.

[ @params = ] N'@parameter_name data_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é qui indique 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 pas de paramètres, @params n’est pas obligatoire. La valeur par défaut de ce paramètre est NULL.

[ @param1 = ] 'value1'

Une valeur pour le 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 texte, ntext et image peuvent être utilisés comme OUTPUT paramètres, sauf si la procédure est une procédure CLR (Common Language Runtime). Un paramètre de sortie qui utilise le OUTPUT mot clé peut être un espace réservé de curseur, sauf si la procédure est une procédure CLR.

[ ... n ]

Un espace réservé destiné aux valeurs de paramètres supplémentaires. Ces valeurs doivent être des constantes ou des variables. Les valeurs ne peuvent pas être des expressions plus complexes telles que des fonctions ou des expressions construites à l'aide d'opérateurs.

Valeurs des codes de retour

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

Jeu de résultats

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

Notes

sp_executesql les paramètres doivent être entrés dans l’ordre spécifique, comme décrit dans la section Syntaxe plus haut dans cet article. Si les paramètres sont entrés dans le désordre, un message d'erreur est émis.

sp_executesql a le même comportement que EXECUTE concernant les lots, l’étendue des noms et le contexte de base de données. L’instruction Transact-SQL ou le sp_executesql lot du paramètre @stmt n’est pas compilé tant que l’instruction sp_executesql n’est pas exécutée. Le contenu de @stmt sont ensuite compilés et exécutés en tant que plan d’exécution distinct du plan d’exécution du lot appelé sp_executesql. Le sp_executesql lot ne peut pas référencer les variables déclarées dans le lot qui appelle sp_executesql. Les curseurs locaux ou les variables du sp_executesql lot ne sont pas visibles par le lot qui appelle sp_executesql. Les modifications dans le contexte de la base de données durent uniquement jusqu'à la fin de l'instruction sp_executesql .

sp_executesql peut être utilisé au lieu de procédures stockées pour exécuter une instruction Transact-SQL plusieurs fois lorsque la modification des valeurs de paramètre à l’instruction est la seule variante. 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. Dans ce scénario, les performances sont équivalentes à celles d’une procédure stockée.

Remarque

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

sp_executesql prend en charge le paramètre de valeurs de paramètre séparément de la chaîne Transact-SQL, comme illustré dans l’exemple suivant.

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

/* Build the SQL string once */
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 AS INT;

DECLARE @SQLString AS NVARCHAR (500);

DECLARE @ParmDefinition AS NVARCHAR (500);

DECLARE @max_title AS 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 remplacer des paramètres offre sp_executesql les avantages suivants par rapport à l’utilisation de l’instruction EXECUTE pour exécuter une chaîne :

• Étant donné que le texte réel de l’instruction Transact-SQL dans la sp_executesql chaîne ne change pas entre les exécutions, l’optimiseur de requête correspond probablement à l’instruction Transact-SQL dans la deuxième exécution avec le plan d’exécution généré pour la première exécution. Par conséquent, SQL Server n’a pas besoin de compiler la deuxième instruction.

• La chaîne Transact-SQL est généré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.

OPTIMIZED_SP_EXECUTESQL

S’applique à : Azure SQL Database

Lorsque la configuration étendue de la base de données OPTIMIZED_SP_EXECUTESQL est activée, le comportement de compilation des lots envoyés à l’aide sp_executesql devient identique au comportement de compilation sérialisé que les objets tels que les procédures stockées et les déclencheurs utilisent actuellement.

Lorsque les lots sont identiques (à l’exclusion des différences de paramètres), l’option OPTIMIZED_SP_EXECUTESQL tente d’obtenir un verrou de compilation en tant que mécanisme d’application pour garantir que le processus de compilation est sérialisé. Ce verrou garantit que si plusieurs sessions appellent sp_executesql simultanément, ces sessions attendent tout en essayant d’obtenir un verrou de compilation exclusif une fois la première session démarrée le processus de compilation. La première exécution des sp_executesql compilations et insère son plan compilé dans le cache du plan. D’autres sessions abandonnent l’attente sur le verrou de compilation et réutilisent le plan une fois qu’il est disponible.

Sans l’option OPTIMIZED_SP_EXECUTESQL , plusieurs appels de lots identiques exécutés via sp_executesql la compilation en parallèle et placent leurs propres copies d’un plan compilé dans le cache du plan, qui remplacent ou dupliquer les entrées du cache de plan dans certains cas.

Remarque

Avant d’activer la configuration étendue à la OPTIMIZED_SP_EXECUTESQL base de données, si les statistiques de mise à jour automatique sont activées, vous devez également activer l’option asynchrone de mise à jour automatique des statistiques avec l’option de configuration étendue à la base de données ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY . L’activation de ces deux options peut réduire considérablement la probabilité que les problèmes de performances liés à des temps de compilation longs, ainsi que des verrous exclusifs de gestionnaire de verrous excessifs (LCK_M_X) et WAIT_ON_SYNC_STATISTICS_REFRESH des attentes.

OPTIMIZED_SP_EXECUTESQL est désactivé par défaut. Pour activer OPTIMIZED_SP_EXECUTESQL au niveau de la base de données, utilisez l’instruction Transact-SQL suivante :

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIMIZED_SP_EXECUTESQL = ON;

autorisations

Nécessite l'appartenance au rôle public .

Exemples

R. Exécuter une instruction SELECT

L’exemple suivant crée et exécute une SELECT instruction qui contient un paramètre incorporé nommé @level.

EXECUTE sp_executesql
    N'SELECT * FROM AdventureWorks2022.HumanResources.Employee
    WHERE BusinessEntityID = @level',
    N'@level TINYINT',
    @level = 109;

B. Exécuter une chaîne généré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 un tableau pour chaque mois de l’année qui a 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 de base pour sp_executesql. L’exemple ne contient pas de vérification des erreurs et n’inclut pas de vérification des règles métier, telles que la garantie que les numéros de commande ne sont pas dupliqués entre les tables.

CREATE PROCEDURE InsertSales @PrmOrderID INT,
    @PrmCustomerID INT,
    @PrmOrderDate DATETIME,
    @PrmDeliveryDate DATETIME
AS
DECLARE @InsertString AS NVARCHAR (500);
DECLARE @OrderMonth AS 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

L’utilisation sp_executesql de cette procédure est plus efficace que l’utilisation EXECUTE de la chaîne générée dynamiquement, car elle permet l’utilisation de marqueurs de paramètres. Les marqueurs de paramètres rendent plus probable que le Moteur de base de données réutilise le plan de requête généré, ce qui permet d’éviter des compilations de requêtes supplémentaires. Avec EXECUTE, chaque INSERT chaîne est unique, car les valeurs de paramètre sont différentes et sont ajoutées à la fin de la chaîne générée dynamiquement. Lorsqu’elle est exécutée, la requête ne serait pas paramétrée de manière à encourager la réutilisation du plan et à être compilée avant l’exécution de chaque INSERT instruction, ce qui ajouterait une entrée mise en cache distincte de la requête dans le cache du plan.

C. Utiliser le paramètre OUTPUT

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

USE AdventureWorks2022;
GO

DECLARE @SQLString AS NVARCHAR (500);
DECLARE @ParmDefinition AS NVARCHAR (500);
DECLARE @SalesOrderNumber AS NVARCHAR (25);
DECLARE @IntVariable AS 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écuter une instruction SELECT

L’exemple suivant crée et exécute une SELECT instruction qui contient un paramètre incorporé nommé @level.

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

Contenu connexe

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