Citation:
Référence de Transact-SQL
sp_executesql
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.
Syntaxe
sp_executesql [@stmt =] stmt
[
{, [@params =] N'@parameter_name data_type [,...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 pouvant implicitement être convertie en ntext. L'utilisation d'expressions Unicode plus complexes (comme par exemple 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 non plus. Si une constante est spécifiée, elle doit contenir le préfixe N. Par exemple, la constante Unicode N'sp_who' est autorisée, mais la constante de caractères '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.
stmt peut contenir des paramètres possédant la même forme qu'un nom de variable, comme par exemple :
N'SELECT * FROM Employees WHERE EmployeeID = @IDParameter'
Chaque paramètre inclus dans stmt doit posséder une entrée correspondante dans la liste de définitions des paramètres @params et dans la liste des valeurs des paramètres.
[@params =] N'@parameter_name data_type [,...n]'
Chaîne contenant les définitions de tous les paramètres incorporés à stmt. Cette chaîne doit être une constante Unicode ou une variable pouvant implicitement être convertie en ntext. Chaque définition de paramètre se compose d'un nom de paramètre et d'un type de données. L'argument n est une marque de réservation 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 contenu(e) dans stmt ne contient aucun paramètre, il est inutile d'utiliser @params. La valeur par défaut de ce paramètre est la valeur NULL.
[@param1 =] 'value1'
Valeur du premier paramètre défini dans la chaîne de paramètres. Cette valeur peut être une constante ou une variable. Chaque paramètre inclus dans stmt doit contenir une valeur de paramètre. Aucune valeur n'est requise si l'instruction ou le lot d'instructions Transact-SQL de stmt ne contient pas de paramètre.
n
Représente une marque de réservation destinée 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 renvoyés
0 (succès) ou 1 (échec)
Jeux de résultats
Renvoie les jeux de résultats de toutes les instructions SQL de la chaîne SQL.
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 du paramètre stmt de sp_executesql n'est compilé(e) 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 locaux ou les variables du lot sp_executesql ne sont pas visibles pour le lot qui a appelé sp_executesql. Les modifications apportées au contexte de base de données ne durent que jusqu'à la fin de l'instruction sp_executesql.
La procédure sp_executesql peut être utilisée en remplacement des 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 elle-même demeurant constante, seules les valeurs des paramètres étant modifiées, l'optimiseur de requête Microsoft® SQL Server™ peut réutiliser le plan d'exécution généré pour la première exécution.
Remarque Si la chaîne d'instruction contient des noms d'objet qui ne sont pas totalement qualifiés, le plan d'exécution n'est pas réutilisé.
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 once.*/
SET @SQLString =
N'SELECT * FROM pubs.dbo.employee WHERE job_lvl = @level'
SET @ParmDefinition = N'@level tinyint'
/* Execute the string with the first parameter value. */
SET @IntVariable = 35
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@level = @IntVariable
/* Execute the same string with the second parameter value. */
SET @IntVariable = 32
EXECUTE sp_executesql @SQLString, @ParmDefinition,
@level = @IntVariable
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 réellement contenu dans l'instruction Transact-SQL de la chaîne sp_executesql ne changeant pas entre les différentes exécutions, l'optimiseur mettra probablement en correspondance l'instruction Transact-SQL de la deuxième exécution et 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
Les autorisations d'exécution reviennent par défaut au rôle public.
Exemple
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é nommé @level.
execute sp_executesql
N'select * from pubs.dbo.employee where job_lvl = @level',
N'@level tinyint',
@level = 35
B. Exécution d'une chaîne créée dynamiquement
Cet exemple illustre l'utilisation de sp_executesql pour exécuter une chaîne créée dynamiquement. Cet exemple de procédure stockée est utilisé pour l'insertion de données dans un ensemble de tables servant à partitionner les données commerciales d'une année. Il existe une table par mois de l'année, au 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)
)
Pour plus d'informations sur l'extraction de données de ces tables partitionnées, voir Utilisation des vues partitionnées.
Le nom de chaque table se compose des trois premières lettres du mois, des quatre chiffres de l'année et de la constante Sales. L'élaboration du nom peut s'effectuer dynamiquement à partir d'une date de commande :
/* Get the first three characters of the month name. */
SUBSTRING( DATENAME(mm, @PrmOrderDate), 1, 3) +
/* Concatenate the four-digit year; cast as character. */
CAST(DATEPART(yy, @PrmOrderDate) AS CHAR(4) ) +
/* Concatenate the constant 'Sales'. */
'Sales'
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. La date de commande est utilisée pour créer le nom de la table devant contenir les données et l'incorporer ensuite à une instruction INSERT. (Il s'agit d'un exemple simple illustrant l'utilisation de sp_executesql. Il ne contient pas de détection d'erreur et n'inclut aucun contrôle des règles de l'entreprise, telles que la recherche de numéros de commandes 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 de 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.
Voir aussi
Lots d'instructions
EXECUTE
Élaboration d'instructions lors de l'exécution
Procédures stockées du système
©1988-2000 Microsoft Corporation. Tous droits réservés.
