AdventureWorks 示例数据库中包含的存储过程

存储过程 dbo.uspGetBillOfMaterials 说明 使用递归查询（公用表表达式）来生成多级物料清单：级别 0 部件的所有级别 1 组件，级别 1 部件的所有级别 2 组件，等等。 使用递归查询（公用表表达式）来返回指定雇员的直接和间接上司。 使用递归查询（公用表表达式）来返回指定经理的直接和间接下属。 在 dbo.ErrorLog 表中记录有关导致跳到 TRY...CATCH 结构 CATCH 块的错误的错误信息。此过程应该在 CATCH 块的范围中执行，否则它不会插入任何错误信息。 显示有关导致跳到 TRY...CATCH 结构 CATCH 块的错误的错误信息。此过程应该在 CATCH 块的范围中执行，否则它不会显示任何错误信息。 使用递归查询（公用表表达式）来返回使用指定产品组件的所有产品部件。例如，返回使用特定类型的车轮或油漆的所有自行车。 更新 Employee 表并使用输入参数中指定的值在 EmployeePayHistory 表中插入了一个新行。 输入@Sta@Chedbo.uspGetEmployeeManagers @Empdbo.uspGetManagerEmployees @Mandbo.uspLogError @Errdbo.uspPrintError 无 dbo.uspGetWhereUsedProductID @Sta@CheuspUpdateEmployeeHireInfo @Emp@Tit@Hir@Rat@Rat@Pay@CuruspUpdateEmployeeLogin 使用所指定 EmployeeID 的输入参数中指定的值来更新 Employee 表。 Empl@Man@Log@Tit@Hir@CuruspUpdateEmployeePersonalInfo 使用所指定 EmployeeID 的输入参数中指定的值来更新 Employee 表。 @Emp@Nat@Bir@Mar@Gen示例

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-67', @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