• 项目

AdventureWorks 中的存储过程

AdventureWorks 示例 OLTP 数据库包含一些 Transact-SQL 存储过程。可以从 Microsoft SQL Server Samples and Community Projects（Microsoft SQL Server 示例和社区项目）主页下载公共语言运行时 (CLR) 存储过程的示例。

CLR 存储过程

下表列出了可供使用的 CLR 存储过程示例。有关 CLR 存储过程的详细信息，请参阅 CLR 存储过程。

示例

说明

AdventureWorks Cycles CLR 层

一个基于 C# 的存储过程，用于可为该存储过程输入 xml 数据，并将该数据插入到 Person.Contact 表的列中。

Transact-SQL 存储过程

下表列出了 AdventureWorks 示例 OLTP 数据库中包含的 Transact-SQL 存储过程。有关 Transact-SQL 存储过程的详细信息，请参阅了解存储过程。

存储过程

说明

输入参数

dbo.uspGetBillOfMaterials

使用递归查询（公用表表达式）来生成多级物料清单：级别 0 部件的所有级别 1 组件，级别 1 部件的所有级别 2 组件，等等。

@StartProductIDint

@CheckDatedatetime

dbo.uspGetEmployeeManagers

使用递归查询（公用表表达式）来返回指定雇员的直接和间接上司。

@EmployeeIDint

dbo.uspGetManagerEmployees

使用递归查询（公用表表达式）来返回指定经理的直接和间接下属。

@ManagerIDint

dbo.uspLogError

在 dbo.ErrorLog 表中记录有关导致跳到 TRY...CATCH 结构 CATCH 块的错误的错误信息。此过程应该在 CATCH 块的范围中执行，否则它不会插入任何错误信息。

@ErrorLogIDint = 0 OUTPUT

dbo.uspPrintError

显示有关导致跳到 TRY...CATCH 结构 CATCH 块的错误的错误信息。此过程应该在 CATCH 块的范围中执行，否则它不会显示任何错误信息。

无

dbo.uspGetWhereUsedProductID

使用递归查询（公用表表达式）来返回使用指定产品组件的所有产品部件。例如，返回使用特定类型的车轮或油漆的所有自行车。

@StartProductIDint

@CheckDatedatetime

uspUpdateEmployeeHireInfo

更新 Employee 表并使用输入参数中指定的值在 EmployeePayHistory 表中插入了一个新行。

@EmployeeIDint

@Titlenvarchar(50)

@HireDatedatetime

@RateChangeDatedatetime

@Ratemoney

@PayFrequencytinyint

@CurrentFlagdbo.Flag

uspUpdateEmployeeLogin

使用所指定 EmployeeID 的输入参数中指定的值来更新 Employee 表。

EmployeeIDint

@ManagerIDint

@LoginIDnvarchar(256)

@Titlenvarchar(50)

@HireDatedatetime

@CurrentFlagdbo.Flag

uspUpdateEmployeePersonalInfo

使用所指定 EmployeeID 的输入参数中指定的值来更新 Employee 表。

@EmployeeID int

@NationalIDNumbernvarchar(15)

@BirthDatedatetime

@MaritalStatusnchar(1)

@Gendernchar(1)

示例

A. 使用 dbo.uspGetBillOfMaterials

下面的示例运行 uspgetBillOfMaterials 存储过程。该过程返回用来生产 Road-550-W Yellow, 44 产品 (ProductID800) 的组件的层次列表。

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

B. 使用 dbo.uspGetEmployeeManagers

下面的示例运行 uspGetEmployeeManagers 存储过程。该过程返回 EmployeeID 50 的直接和间接上司的层次列表。

USE AdventureWorks;
GO
EXEC dbo.uspGetEmployeeManagers 50;

C. 使用 dbo.uspGetManagerEmployees

下面的示例运行 uspGetManagerEmployees 存储过程。该过程返回 ManagerID 140 的直接和间接下属的层次列表。

USE AdventureWorks;
GO
EXEC dbo.uspGetManagerEmployees 140;

D. 使用 dbo.uspGetWhereUsedProductID

下面的示例运行 usp_getWhereUsedProductID 存储过程。该过程返回使用产品 ML Road Front Wheel (ProductID 819) 的所有产品。

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

E. 使用 HumanResources.uspUpdateEmployeeHireInfo

下面的示例运行 uspUpdateEmployeeHireInfo 存储过程。该过程根据指定的 EmployeeID 更新 Employee 表中的 Title、HireDate 和 Current Flag 列，并使用 EmployeeID、RateChangeDate、Rate 和 PayFrequency 的值在 EmployeePayHistory 表中插入一个新行。必须指定所有参数值。

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

F. 使用 HumanResources.uspUpdateEmployeeLogin

下面的示例运行 uspUpdateEmployeeLogin 存储过程。该过程根据 EmployeeID 6 更新 Employee 表中的 ManagerID, LoginID, Title、HireDate 和 Current Flag 列。必须指定所有参数值。

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. 使用 HumanResources.uspUpdateEmployeePersonalInfo

下面的示例运行 uspUpdateEmployeePersonalInfo 存储过程。该过程根据 EmployeeID 6 更新 Employee 表中的 NationalIDNumber、BirthDate、MaritalStatue 和 Gender 列。必须指定所有参数值。

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

H. 使用 dbo.uspLogError

下面的示例尝试从 Production.Product 表中删除产品 Mountain-400-W Silver, 38 (ProductID 980)。表的 FOREIGN KEY 约束不允许删除操作，约束冲突错误将把控制传递给 CATCH 块。CATCH 块中的代码首先检查所有活动的事务并回滚这些活动事务，然后再执行 uspLogError 存储过程。此过程在 ErrorLog 表中输入错误信息，并返回插入到 @ErrorLogID OUTPUT 参数中的行的 ErrorLogID。@ErrorLogID 参数的默认值为 0。然后查询 ErrorLog 表以查看存储过程的结果。

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. 使用 dbo.uspPrintError

下面的示例尝试从 Production.Product 表中删除产品 Mountain-400-W Silver, 38 (ProductID980)。表的 FOREIGN KEY 约束不允许删除操作，约束冲突错误将把控制传递给 CATCH 块。CATCH 块中的代码执行 uspPrintError 存储过程。此过程会显示错误信息。

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