次の方法で共有


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

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 列を更新し、EmployeeIDRateChangeDateRate、および 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 の参考資料の入手