• Article

Exécution de procédures stockées (Moteur de base de données)

Pour exécuter une procédure stockée, utilisez l'instruction EXECUTE Transact-SQL. Vous pouvez aussi exécuter une procédure stockée sans recourir au mot clé EXECUTE si la procédure stockée est la première instruction du traitement.

Exécution de procédures stockées système

Les procédures stockées système commencent par les caractères sp_. Elles sont stockées physiquement dans la base de données de ressources, mais elles apparaissent logiquement dans le schéma sys de chaque base de données définie par le système et définie par l'utilisateur dans l'instance de SQL Server. Elles peuvent s'exécuter depuis n'importe quelle base de données, sans qu'il soit nécessaire de qualifier complètement le nom de la procédure stockée. Un nom qualifié sans schéma est soit un nom en une partie, tel que sp_someproc, soit un nom en trois parties, tel que somedb..sp_someproc, la seconde partie (le nom du schéma) n'étant pas spécifiée.

Nous vous conseillons de qualifier tous les noms de procédures stockées système à l'aide du nom de schéma sys pour éviter les conflits de nom. L'exemple suivant illustre la méthode recommandée pour l'exécution d'une procédure stockée système.

EXEC sys.sp_who;

Les exemples suivants illustrent les méthodes de compatibilité ascendante pour l'exécution de procédures stockées système.

[!REMARQUE]

Les méthodes d'exécution des procédures stockées système ci-après seront supprimées dans une version ultérieure de SQL Server. Évitez d'utiliser ces méthodes dans vos nouveaux travaux de développement, et prévoyez de modifier les applications qui les utilisent actuellement.

EXEC sp_who;
EXEC master.dbo.sp_who;
EXEC mydatabase..sp_who;
EXEC dbo.sp_who;
EXEC mydatabase.dbo.sp_who;

Correspondance de classement de base de données

SQL Server 2008 utilise le classement de la base de données d'appel pour mettre en correspondance les noms de procédures stockées système. Par conséquent, vous devez systématiquement utiliser la casse exacte des noms de procédures système dans votre application. Par exemple, le code suivant ne fonctionnera pas s'il est exécuté dans le contexte d'une base de données dotée d'un classement qui respecte la casse :

exec SP_heLP; -- Will fail to resolve because SP_heLP does not equal sp_help

Utilisez les vues de catalogue sys.system_objects et sys.system_parameters pour afficher le nom exact des procédures stockées système.

Exécution de procédures stockées système étendues

Les procédures stockées système étendues commencent par les caractères xp_. Elles sont stockées physiquement dans la base de données de ressources, mais elles apparaissent logiquement dans le schéma sys de chaque base de données définie par le système et définie par l'utilisateur dans l'instance de SQL Server. L'exemple suivant illustre la méthode recommandée pour l'exécution d'une procédure stockée système étendue.

EXEC sys.xp_subdirs 'c:\';

Exécution de procédures stockées définies par l'utilisateur

Lors de l'exécution d'une procédure stockée définie par l'utilisateur (dans un traitement ou dans un module, tel qu'une procédure stockée ou une fonction définie par l'utilisateur), nous vous conseillons vivement de qualifier le nom de la procédure stockée à l'aide du nom de schéma au minimum.

L'exemple suivant illustre la méthode recommandée pour l'exécution d'une procédure stockée définie par l'utilisateur.

USE AdventureWorks;
GO
EXEC dbo.uspGetEmployeeManagers 50;

-ou-

EXEC AdventureWorks.dbo.uspGetEmployeeManagers 50;
GO

Si une procédure stockée non qualifiée définie par l'utilisateur est spécifiée, le moteur de base de données la recherche dans l'ordre suivant :

  • Schéma sys de la base de données actuelle.

  • Le schéma par défaut de l'appelant est exécuté dans un traitement ou dans SQL dynamique ; ou, si le nom de procédure stockée non qualifié apparaît dans le corps d'une autre définition de procédure, le schéma contenant cette autre procédure est recherché par la suite. Pour plus d'informations sur les schémas par défaut, consultez la rubrique Séparation du schéma et de l'utilisateur.

  • Schéma dbo dans la base de données actuelle.

Important

Si une procédure stockée créée par un utilisateur porte le même nom qu'une procédure stockée système, celle de l'utilisateur ne s'exécutera jamais si vous utilisez une référence de nom qualifié sans schéma. Pour plus d'informations, consultez Création de procédures stockées (Moteur de base de données).

Spécification des paramètres

Des valeurs de paramètres peuvent être fournies si une procédure stockée a été écrite pour les accepter.

La valeur indiquée doit être une constante ou une variable ; vous ne pouvez pas spécifier un nom de fonction comme valeur d'un paramètre. Les variables peuvent être des variables système ou des variables définies par l'utilisateur, telles que @@spid.

Les exemples suivants illustrent la transmission de valeurs de paramètres à la procédure stockée uspGetWhereUsedProductID. La procédure attend des valeurs pour deux paramètres d'entrée : un ID de produit et une date. Les exemples indiquent comment transmettre des paramètres sous forme de constantes et de variables. Ils décrivent également comment utiliser une variable pour transmettre la valeur d'une fonction.

USE AdventureWorks;
GO
-- Passing values as constants.
EXEC dbo.uspGetWhereUsedProductID 819, '20050225';
GO
-- Passing values as variables.
DECLARE @ProductID int, @CheckDate datetime;
SET @ProductID = 819;
SET @CheckDate = '20050225';
EXEC dbo.uspGetWhereUsedProductID @ProductID, @CheckDate;
GO
-- Try to use a function as a parameter value.
-- This produces an error message.
EXEC dbo.uspGetWhereUsedProductID 819, GETDATE();
GO
-- Passing the function value as a variable.
DECLARE @CheckDate datetime;
SET @CheckDate = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;
GO

Vous devez nommer les paramètres pour les spécifier dans un ordre différent que celui défini par la procédure stockée. Pour plus d'informations, consultez Spécification d'un nom de paramètre.

Pour spécifier qu'un paramètre doit retourner une valeur au programme appelant, utilisez le mot clé OUTPUT. Pour plus d'informations, consultez Spécification de la direction d'un paramètre.

Spécification de l'ordre des paramètres

Si vous entrez des paramètres sous la forme **@parameter =**value, leur ordre n'a pas d'importance. Vous pouvez aussi omettre les paramètres pour lesquels des valeurs par défaut ont été indiquées. Si vous spécifiez un paramètre sous la forme **@parameter =**value, vous devez tous les spécifier de cette façon. Sinon, vous devez les entrer dans l'ordre indiqué par l'instruction CREATE PROCEDURE.

Lorsque le serveur exécute une procédure stockée, il refuse tous les paramètres qui n'étaient pas inclus dans la liste des paramètres au moment de la création de la procédure. Tout paramètre qui est transmis par référence (en fournissant explicitement son nom) n'est pas accepté si son nom ne concorde pas.

Utilisation de valeurs par défaut dans les paramètres

Bien que vous puissiez omettre des paramètres ayant des valeurs par défaut, seule la liste des paramètres peut être tronquée. Par exemple, si une procédure stockée comporte cinq paramètres, vous pouvez omettre les deux derniers paramètres, mais vous ne pouvez pas omettre le quatrième et inclure le cinquième, à moins d'utiliser le format **@parameter =**value.

La valeur par défaut d'un paramètre, si elle a été définie dans la procédure stockée, est utilisée dans les cas suivants :

  • Aucune valeur n'est spécifiée pour le paramètre au moment de l'exécution de la procédure stockée.

  • Le mot clé DEFAULT est spécifié comme valeur du paramètre.