Procédures stockées dans AdventureWorks

L'exemple de base de données OLTP AdventureWorks comporte plusieurs procédures stockées Transact-SQL. Des exemples de procédures stockées CLR (Common Language Runtime) peuvent être téléchargés à partir de la page d'accueil Microsoft SQL Server Samples and Community Projects (page éventuellement en anglais).

Procédures stockées CLR

Le tableau ci-dessous répertorie les exemples de procédures stockées CLR qui sont disponibles. Pour plus d'informations sur les procédures stockées CLR, consultez Procédures stockées du CLR.

Aperçu	Description
Couche CLR AdventureWorks Cycles	Procédure stockée écrite en C# qui prend des données xml comme valeurs d'entrée et insère ces données dans les colonnes de la table Person.Contact.

Procédures stockées Transact-SQL

Le tableau ci-dessous répertorie les procédures stockées Transact-SQL qui sont incluses dans l'exemple de base de données OLTP AdventureWorks. Pour plus d'informations sur les procédures stockées Transact-SQL, consultez Fonctionnement des procédures stockées.

Procédure stockée	Description	Paramètres d'entrée
dbo.uspGetBillOfMaterials	Utilise une requête récursive (expression de table commune) pour générer une nomenclature à plusieurs niveaux : tous les composants de niveau 1 d'un assembly de niveau 0, tous les composants de niveau 2 d'un assembly de niveau 1, etc.	@StartProductIDint @CheckDatedatetime
dbo.uspGetEmployeeManagers	Utilise une requête récursive (expression courante de table) pour retourner les responsables directs et indirects des employés spécifiés.	@EmployeeIDint
dbo.uspGetManagerEmployees	Utilise une requête récursive (expression courante de table) pour retourner les employés directs et indirects des responsables spécifiés.	@ManagerIDint
dbo.uspLogError	Consigne les informations d'erreur dans la table dbo.ErrorLog concernant l'erreur qui a provoqué le brusque passage de l'exécution au bloc CATCH d'une construction TRY...CATCH. Cette procédure doit être exécutée à partir d'un bloc CATCH ; sinon, elle revient sans insérer les informations d'erreur.	@ErrorLogIDint = 0 OUTPUT
dbo.uspPrintError	Imprime les informations d'erreur concernant l'erreur qui a provoqué le brusque passage de l'exécution au bloc CATCH d'une construction TRY...CATCH. Cette procédure doit être exécutée à partir d'un bloc CATCH ; sinon, elle revient sans imprimer les informations d'erreur.	Aucun
dbo.uspGetWhereUsedProductID	Utilise une requête récursive (expression courante de table) pour retourner tout les assemblages de produit qui utilisent le composant de produit spécifié. Par exemple, retourner toutes les bicyclettes qui utilisent une roue ou un type de peinture spécifique.	@StartProductIDint @CheckDatedatetime
uspUpdateEmployeeHireInfo	Met à jour la table Employee et insert une nouvelle ligne dans la table EmployeePayHistory avec les valeurs spécifiées dans les paramètres d'entrée.	@EmployeeIDint @Titlenvarchar(50) @HireDatedatetime @RateChangeDatedatetime @Ratemoney @PayFrequencytinyint @CurrentFlagdbo.Flag
uspUpdateEmployeeLogin	Met à jour la table Employee avec les valeurs spécifiées dans les paramètres d'entrée pour un EmployeeID donné.	@EmployeeID int @ManagerIDint @LoginIDnvarchar(256) @Titlenvarchar(50) @HireDatedatetime @CurrentFlagdbo.Flag
uspUpdateEmployeePersonalInfo	Met à jour la table Employee avec les valeurs spécifiées dans les paramètres d'entrée pour un EmployeeID donné.	@EmployeeID int @NationalIDNumbernvarchar(15) @BirthDatedatetime @MaritalStatusnchar(1) @Gendernchar(1)

Exemple

A. Utilisation de dbo.uspGetBillOfMaterials

L'exemple suivant exécute la procédure stockée uspgetBillOfMaterials. La procédure retourne la liste hiérarchique des composants utilisés pour fabriquer le produit Road-550-W Yellow, 44 (ProductID800).

USE AdventureWorks;
GO
DECLARE @CheckDate DATETIME;
SET @CheckDate = GETDATE();
EXEC [AdventureWorks].[dbo].[uspGetBillOfMaterials] 800, @CheckDate;

B. Utilisation de dbo.uspGetEmployeeManagers

L'exemple suivant exécute la procédure stockée uspGetEmployeeManagers. La procédure retourne la liste hiérarchique des responsables directs et indirects de l'EmployeeID 50.

USE AdventureWorks;
GO
EXEC dbo.uspGetEmployeeManagers 50;

C. Utilisation de dbo.uspGetManagerEmployees

L'exemple suivant exécute la procédure stockée uspGetManagerEmployees. La procédure retourne la liste hiérarchique des employés directs et indirects du ManagerID 140.

USE AdventureWorks;
GO
EXEC dbo.uspGetManagerEmployees 140;

D. Utilisation de dbo.uspGetWhereUsedProductID

L'exemple suivant exécute la procédure stockée usp_getWhereUsedProductID. La procédure retourne tous les produits qui utilisent le produit ML Road Front Wheel (ProductID 819)

USE AdventureWorks
GO
DECLARE @CheckDate datetime;
SET @CheckDate = GETDATE();
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;

E. Utilisation de HumanResources.uspUpdateEmployeeHireInfo

L'exemple suivant exécute la procédure stockée uspUpdateEmployeeHireInfo. La procédure met à jour les colonnes Title, HireDate et Current Flag dans la table Employee pour l'EmployeeID spécifié et insert une nouvelle ligne dans la table EmployeePayHistory avec les valeurs de EmployeeID, RateChangeDate, Rate et PayFrequency. Toutes les valeurs de paramètres doivent être spécifiées.

USE AdventureWorks;
GO
EXEC [HumanResources].[uspUpdateEmployeeHireInfo]
    @EmployeeID = 109, 
    @Title = N'President', 
    @HireDate = '19980513',
    @RateChangeDate = '20041208', 
    @Rate = 50.00, 
    @PayFrequency = 1, 
    @CurrentFlag = 1;

F. Utilisation de HumanResources.uspUpdateEmployeeLogin

L'exemple suivant exécute la procédure stockée uspUpdateEmployeeLogin. La procédure met à jour les colonnes ManagerID, LoginID, Title, HireDate et Current Flag dans la table Employee pour l'EmployeeID 6. Toutes les valeurs de paramètres doivent être spécifiées.

USE AdventureWorks;
GO
DECLARE @HireDate datetime;
SET @HireDate = CONVERT(DATETIME,'19990619',101);
EXEC HumanResources.uspUpdateEmployeeLogin
    @EmployeeID = 6, 
    @ManagerID = 273,
    @LoginID = N'adventure-works\david01',
    @Title = N'Marketing Vice President', 
    @HireDate = @HireDate,
    @CurrentFlag = 1 ;

G. Utilisation de HumanResources.uspUpdateEmployeePersonalInfo

L'exemple suivant exécute la procédure stockée uspUpdateEmployeePersonalInfo. La procédure met à jour les colonnes NationalIDNumber, BirthDate, MaritalStatue et Gender dans la table Employee pour l'EmployeeID 6. Toutes les valeurs de paramètres doivent être spécifiées.

USE AdventureWorks;
GO
EXEC HumanResources.uspUpdateEmployeePersonalInfo
    @EmployeeID = 6, 
    @NationalIDNumber = N'123-45-6789',
    @BirthDate = '19651030',
    @MaritalStatus = N'S', 
    @Gender = N'M';
GO

H. Utilisation de dbo.uspLogError

L'exemple suivant tente de supprimer le produit Mountain-400-W Silver, 38 (ProductID 980) de la table Production.Product. L'opération de suppression échoue en raison d'une contrainte FOREIGN KEY sur la table, et l'erreur de violation de contrainte transmet le contrôle au bloc CATCH. Le code à l'intérieur du bloc CATCH commence par rechercher d'éventuelles transactions actives pour les annuler avant d'exécuter les procédures stockées uspLogError. Cette procédure insert les informations d'erreur dans la table ErrorLog et retourne le ErrorLogID de la ligne insérée dans le paramètre @ErrorLogID OUTPUT. La valeur par défaut du paramètre @ErrorLogID est 0. La table ErrorLog fait alors l'objet d'une requête pour afficher les résultats de la procédure stockée.

USE AdventureWorks;
GO
BEGIN TRY
    BEGIN TRANSACTION;
    DELETE FROM Production.Product
        WHERE ProductID = 980;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION;
    DECLARE @ErrorLogID INT;
    EXECUTE dbo.uspLogError @ErrorLogID = @ErrorLogID OUTPUT;
END CATCH;
GO
--View the results of the uspLogError stored procedure
SELECT * FROM dbo.ErrorLog;

I. Utilisation de dbo.uspPrintError

L'exemple suivant tente de supprimer le produit Mountain-400-W Silver, 38 (ProductID980) de la table Production.Product. L'opération de suppression échoue en raison d'une contrainte FOREIGN KEY sur la table, et l'erreur de violation de contrainte transmet le contrôle au bloc CATCH. Le code à l'intérieur du bloc CATCH exécute la procédure stockée uspPrintError. Cette procédure imprime les informations d'erreur.

USE AdventureWorks;
GO
BEGIN TRY
    DELETE FROM Production.Product
        WHERE ProductID = 980;
END TRY
BEGIN CATCH
    EXECUTE dbo.uspPrintError;
END CATCH;
GO

Voir aussi

Tâches

Exemples pour le moteur de base de données SQL Server

Référence

CREATE PROCEDURE (Transact-SQL)

TRY...CATCH (Transact-SQL)

Autres ressources

Objets SQL Server dans AdventureWorks