發行項
09/24/2010

AdventureWorks 中的預存程序

AdventureWorks 範例 OLTP 資料庫包含數個 Transact-SQL 預存程序。您可以從 Microsoft SQL Server 範例和社群專案 (英文) 首頁下載 Common Language Runtime (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 資料表。	@EmployeeIDint @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" (ML 道路型前輪) (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

請參閱