AdventureWorks のストアドプロシージャ

[アーティクル]
12/15/2008

AdventureWorks のサンプル OLTP データベースには、いくつかの Transact-SQL ストアドプロシージャが用意されています。共通言語ランタイム (CLR) ストアドプロシージャの例については、「CLR プログラミングサンプル」を参照してください。

CLR ストアドプロシージャ

次の表では、利用可能な CLR ストアドプロシージャのサンプルについて説明します。CLR ストアドプロシージャの詳細については、「CLR ストアドプロシージャ」を参照してください。

サンプル	説明
AdventureWorks Cycles CLR レイヤ	xml データを入力として取得し、Person.Contact テーブルの列にそのデータを挿入する C# ベースのストアドプロシージャです。
ラージオブジェクト (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 という製品 (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 列、および Current Flag 列を更新します。すべてのパラメータ値を指定する必要があります。

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 データベースエンジンサンプル
 TRY...CATCH (Transact-SQL)

ヘルプおよび情報

SQL Server 2005 の参考資料の入手

AdventureWorks のストアド プロシージャ

CLR ストアド プロシージャ

Transact-SQL ストアド プロシージャ

例