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

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

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

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, Department
    FROM HumanResources.vEmployeeDepartmentHistory;
GO

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

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, Department
    FROM HumanResources.vEmployeeDepartmentHistory
    WHERE FirstName = @FirstName AND LastName = @LastName;
GO

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

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, Department
    FROM HumanResources.vEmployeeDepartmentHistory
    WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;
GO

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

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

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)))+'.'

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

Tâches

Référence

Concepts

Autres ressources