AdventureWorks のストアド プロシージャ
AdventureWorks のサンプル OLTP データベースには、いくつかの Transact-SQL ストアド プロシージャが用意されています。共通言語ランタイム (CLR) ストアド プロシージャの例については、「CLR プログラミング サンプル」を参照してください。
CLR ストアド プロシージャ
次の表では、利用可能な CLR ストアド プロシージャのサンプルについて説明します。CLR ストアド プロシージャの詳細については、「CLR ストアド プロシージャ」を参照してください。
| サンプル | 説明 |
|---|---|
xml データを入力として取得し、Person.Contact テーブルの列にそのデータを挿入する C# ベースのストアド プロシージャです。 |
|
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)