Création de procédures stockées (moteur de base de données)

Article
12/15/2008

Vous pouvez créer des procédures stockées à l'aide de l'instruction Transact-SQL CREATE PROCEDURE.

Lisez les informations ci-dessous avant de créer une procédure stockée.

L'instruction CREATE PROCEDURE ne peut pas s'utiliser conjointement avec d'autres instructions SQL dans un même lot d'instructions.
Pour créer des procédures, vous devez disposer de l'autorisation CREATE PROCEDURE dans la base de données et de l'autorisation ALTER sur le schéma dans lequel la procédure est créée. Pour les procédures stockées CLR, vous devez être propriétaire de l'assembly référencé dans <method_specifier> ou disposer de la permission REFERENCES sur cet assembly.
Les procédures stockées sont des objets dont l'étendue est le schéma et leur nom doit respecter les règles relatives aux identificateurs.
Vous ne pouvez créer une procédure stockée que dans la base de données actuelle.

Pour créer une procédure stockée, vous devez préciser :

les paramètres d'entrée et de sortie de la procédure ou du lot appelant ;
les instructions de programmation qui exécutent les opérations dans la base de données, y compris l'appel à d'autres procédures ;
la valeur d'état retournée à la procédure ou au lot appelant pour indiquer la réussite ou l'échec et, dans ce cas, la raison de l'échec.
les instructions de traitement d'erreur nécessaires pour détecter et traiter les erreurs potentielles.
Microsoft SQL Server 2005 introduit de nouvelles fonctions de traitement des erreurs, telles que ERROR_LINE and ERROR_PROCEDURE, qui peuvent être spécifiées dans la procédure stockée. Pour plus d'informations, consultez Utilisation de TRY...CATCH dans Transact-SQL.

Attribution d'un nom aux procédures stockées

Nous vous conseillons vivement de ne pas créer de procédures stockées en utilisant sp_ comme préfixe. SQL Server se sert du préfixe sp_ pour désigner les procédures stockées système. Le nom que vous choisissez peut entrer en conflit avec une procédure stockée système future. Si votre application utilise des références de nom qualifié non-schéma et si le nom de votre propre procédure est en conflit avec le nom d'une procédure système, votre application va échouer car le nom est lié à la procédure système et non à votre procédure.

Une procédure stockée définie par l'utilisateur qui comporte le même nom qu'une procédure stockée système et qui est non qualifiée ou qui réside dans le schéma dbo ne sera jamais exécutée ; c'est toujours la procédure stockée système qui sera exécutée à la place. L'exemple ci-dessous illustre ce comportement.

USE AdventureWorks;
GO
CREATE PROCEDURE dbo.sp_who
AS
    SELECT FirstName, LastName FROM Person.Contact;
GO
EXEC sp_who;
EXEC dbo.sp_who;
GO
DROP PROCEDURE dbo.sp_who;
GO

L'utilisation d'un qualificateur de schéma explicite produit également un léger avantage en termes de performances. La résolution de nom est légèrement plus rapide si le Moteur de base de données n'a pas à rechercher dans plusieurs schémas pour trouver la procédure. Pour plus d'informations, consultez Exécution d'une procédure stockée.

Procédures stockées temporaires

Les procédures stockées temporaires privées et globales, comme les tables temporaires, peuvent être créées en ajoutant les préfixes # et ## à leur nom. # désigne une procédure stockée temporaire locale, et ##, une procédure stockée temporaire globale. Ces procédures n'existent plus après l'arrêt de SQL Server.

Les procédures stockées temporaires sont utiles lorsque vous vous connectez à des versions antérieures de SQL Server qui ne prennent pas en charge la réutilisation des plans d'exécution des instructions ou des lots d'instructions Transact-SQL. L'application permettant de se connecter à SQL Server 2000 et versions ultérieures doit utiliser la procédure stockée système sp_executesql au lieu de procédures stockées temporaires. Seule la connexion qui a créé une procédure temporaire locale peut exécuter la procédure, laquelle est automatiquement supprimée au moment de la déconnexion.

Toute connexion peut exécuter une procédure stockée temporaire globale. Une procédure stockée temporaire globale existe jusqu'à ce que l'utilisateur qui l'a créée se déconnecte ; les versions de cette procédure en cours d'exécution par d'autres utilisateurs s'achèvent. Lorsque la connexion utilisée pour créer la procédure est fermée, aucune nouvelle exécution de cette procédure temporaire globale n'est possible. Seules les connexions ayant déjà lancé l'exécution de la procédure stockée peuvent la terminer.

Si une procédure stockée est créée directement dans la base de données tempdb sans le préfixe # ou ##, elle est automatiquement supprimée à l'arrêt de SQL Server, parce que la base de données tempdb est recréée à chaque démarrage de SQL Server. Les procédures créées directement dans la base de données tempdb continuent d'exister même au terme de la connexion qui l'a créée.

Remarque :
Une utilisation intensive de procédures stockées temporaires peut créer des conflits sur les tables système de tempdb et réduire les performances. Il est recommandé d'utiliser sp_executesql à la place. sp_executesql ne stocke pas les données dans les tables système et évite ainsi le problème.

Les procédures stockées CLR ne peuvent pas être créées en tant que procédures stockées temporaires.

Exemples

A. Utilisation d'une procédure simple avec une instruction SELECT complexe

La procédure stockée suivante retourne tous les salariés (nom et prénom), leur titre et le nom de leur service à partir d'une vue. Cette procédure stockée n'utilise aucun paramètre.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SET NOCOUNT ON;
    SELECT LastName, FirstName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment;
GO

L'exécution de la procédure stockée uspGetEmployees peut s'effectuer selon plusieurs combinaisons :

EXECUTE HumanResources.uspGetAllEmployees;
GO
-- Or
EXEC HumanResources.uspGetAllEmployees;
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetAllEmployees;

B. Utilisation d'une procédure simple avec des paramètres

La procédure stockée suivante retourne uniquement le salarié spécifié (nom et prénom), son titre et le nom de son service à partir d'une vue. Cette procédure stockée accepte les correspondances exactes pour les paramètres passés.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees 
    @LastName nvarchar(50), 
    @FirstName nvarchar(50) 
AS 
    
    SET NOCOUNT ON;
    SELECT FirstName, LastName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName = @FirstName AND LastName = @LastName;
GO

L'exécution de la procédure stockée uspGetEmployees peut s'effectuer selon plusieurs combinaisons :

EXECUTE HumanResources.uspGetEmployees N'Ackerman', N'Pilar';
-- Or
EXEC HumanResources.uspGetEmployees @LastName = N'Ackerman', @FirstName = N'Pilar';
GO
-- Or
EXECUTE HumanResources.uspGetEmployees @FirstName = N'Pilar', @LastName = N'Ackerman';
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetEmployees N'Ackerman', N'Pilar';

C. Utilisation d'une procédure simple avec des paramètres génériques

La procédure stockée suivante retourne uniquement les salariés spécifiés (nom et prénom), leur titre et leur service à partir d'une vue. Ce modèle de procédure stockée fait correspondre les paramètres transmis ou, s'ils ne sont pas fournis, utilise les valeurs par défaut prédéfinies (noms commençant par la lettre D).

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetEmployees2', 'P' ) IS NOT NULL 
    DROP PROCEDURE HumanResources.uspGetEmployees2;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees2 
    @LastName nvarchar(50) = N'D%', 
    @FirstName nvarchar(50) = N'%'
AS 
    SET NOCOUNT ON;
    SELECT FirstName, LastName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;
GO

L'exécution de la procédure stockée uspGetEmployees2 peut s'effectuer selon plusieurs combinaisons. Vous trouverez ci-dessous certaines de ces combinaisons :

EXECUTE HumanResources.uspGetEmployees2;
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Wi%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 @FirstName = N'%';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'[CK]ars[OE]n';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'Hesse', N'Stefen';
-- Or
EXECUTE HumanResources.uspGetEmployees2 N'H%', N'S%';

D. Utilisation des paramètres OUTPUT

L'exemple suivant crée la procédure stockée uspGetList, qui retourne la liste des produits dont le prix ne dépasse pas un montant spécifié. Cet exemple montre l'utilisation de plusieurs instructions SELECT et de plusieurs paramètres OUTPUT. Les paramètres OUTPUT permettent à une procédure externe, un lot d'instructions ou plusieurs instructions Transact-SQL d'accéder à un ensemble de valeurs pendant l'exécution de la procédure.

USE AdventureWorks;
GO
IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL 
    DROP PROCEDURE Production.uspGetList;
GO
CREATE PROCEDURE Production.uspGetList @Product varchar(40) 
    , @MaxPrice money 
    , @ComparePrice money OUTPUT
    , @ListPrice money OUT
AS
    SET NOCOUNT ON;
    SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
    FROM Production.Product AS p
    JOIN Production.ProductSubcategory AS s 
      ON p.ProductSubcategoryID = s.ProductSubcategoryID
    WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
-- Populate the output variable @ListPprice.
SET @ListPrice = (SELECT MAX(p.ListPrice)
        FROM Production.Product AS p
        JOIN  Production.ProductSubcategory AS s 
          ON p.ProductSubcategoryID = s.ProductSubcategoryID
        WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
-- Populate the output variable @compareprice.
SET @ComparePrice = @MaxPrice;
GO

Exécutez uspGetList pour retourner la liste des produits Adventure Works (Vélos) qui coûtent moins de 700 $. Les paramètres OUTPUT @Cost et @ComparePrices sont utilisés avec le langage de contrôle de flux pour retourner un message dans la fenêtre Messages.

Remarque :
La variable OUTPUT doit être définie au cours de la création de la procédure ainsi que pendant l'utilisation de la variable. Les noms du paramètre et de la variable ne doivent pas nécessairement correspondre contrairement au type de données et à la position du paramètre qui, eux, doivent concorder (sauf si vous utilisez @ListPrice= variable).

DECLARE @ComparePrice money, @Cost money 
EXECUTE Production.uspGetList '%Bikes%', 700, 
    @ComparePrice OUT, 
    @Cost OUTPUT
IF @Cost <= @ComparePrice 
BEGIN
    PRINT 'These products can be purchased for less than 
    $'+RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'
END
ELSE
    PRINT 'The prices for all products in this category exceed 
    $'+ RTRIM(CAST(@ComparePrice AS varchar(20)))+'.'

Voici le jeu de résultats partiel :

Product                                            List Price
-------------------------------------------------- ------------------
Road-750 Black, 58                                 539.99
Mountain-500 Silver, 40                            564.99
Mountain-500 Silver, 42                            564.99
...
Road-750 Black, 48                                 539.99
Road-750 Black, 52                                 539.99

(14 row(s) affected)

These items can be purchased for less than $700.00.

Voir aussi

Partager via

Création de procédures stockées (moteur de base de données)

Attribution d'un nom aux procédures stockées

Procédures stockées temporaires

Exemples

A. Utilisation d'une procédure simple avec une instruction SELECT complexe

B. Utilisation d'une procédure simple avec des paramètres

C. Utilisation d'une procédure simple avec des paramètres génériques

D. Utilisation des paramètres OUTPUT

Voir aussi

Tâches

Concepts

Autres ressources

Aide et Informations

Ressources supplémentaires