共用方式為

AdventureWorks 中的預存程序

  • 發行項

AdventureWorks 範例 OLTP 資料庫包含數個 Transact-SQL 預存程序。＜CLR 程式設計範例＞中提供 Common Language Runtime (CLR) 預存程序的範例。

CLR 預存程序

下表列出可用的 CLR 預存程序範例。如需有關 CLR 預存程序的詳細資訊，請參閱＜CLR Stored Procedures＞。

範例 描述

AdventureWorks Cycles CLR 層

C# 架構的預存程序，在其輸入時接受 xml 資料，並將資料插入到 Person.Contact 資料表的資料行中。

大型物件 (LOB) 處理

示範如何使用 CLR 預存程序以及從 CLR 預存程序呼叫 Transact-SQL 預存程序。

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 資料表。

@EmployeeID int

@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" (ProductID``800) 之元件的階層式清單。

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" (ProductID``980) 產品。資料表上的 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

請參閱

其他資源

AdventureWorks 中的 SQL Server 物件
CREATE PROCEDURE (Transact-SQL)
SQL Server Database Engine 範例
TRY...CATCH (Transact-SQL)

說明及資訊

取得 SQL Server 2005 協助