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

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 utilisent l’exemple AdventureWorks2022 de base de données, que vous pouvez télécharger à partir de la page d’accueil microsoft SQL Server Samples and Community Projects .

Arguments

[ @stmt = ] N’statement'

Chaîne Unicode qui contient une instruction Transact-SQL ou un lot. @stmt doit être une constante Unicode ou une variable Unicode. Les expressions Unicode plus complexes, telles que la concaténation de deux chaînes avec l’opérateur + , ne sont pas autorisées. 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 N'sp_who' Unicode est valide, mais la constante 'sp_who' de caractère 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 avoir une entrée correspondante dans la liste de définitions de paramètres @params et la liste des valeurs de paramètre.

[ @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 est un espace réservé qui indique plus de 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'

Valeur du premier paramètre défini dans la chaîne de paramètre. Cette valeur peut être une constante ou une variable Unicode. Il doit y avoir une valeur de paramètre fournie pour chaque paramètre inclus dans @stmt. Les valeurs ne sont pas requises 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 ]

Espace réservé pour les valeurs des 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 générées à l’aide d’opérateurs.

Valeurs des codes de retour

0 (réussite) ou non 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 hors ordre, un message d’erreur se produit.

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 lot dans le sp_executesqlparamè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.

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 INT;
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition 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 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 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.

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.

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

L’utilisation sp_executesql de cette procédure est plus efficace que EXECUTE d’utiliser pour exécuter une chaîne. Lorsqu’elle sp_executesql est utilisée, il n’existe que 12 versions de la INSERT chaîne générées, une pour chaque table mensuelle. Avec EXECUTE, chaque INSERT chaîne est unique, car les valeurs de paramètre sont différentes. Bien que les deux méthodes génèrent le même nombre de lots, la similarité des INSERT chaînes générées par sp_executesql rend plus probable que l’optimiseur de requête réutilise les plans d’exécution.

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