ストアド プロシージャからデータを返す
適用対象:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)
プロシージャからのデータを呼び出し元のプログラムに返す手段には、結果セット、出力パラメーター、リターン コードの 3 つがあります。 この記事では、3 つのアプローチについて説明します。
結果セットを使用してデータを返す
ストアド プロシージャの本文に SELECT ステートメントを含める場合 (ただし、SELECT ...INTO または INSERT ...SELECT)、SELECT ステートメントで指定された行はクライアントに直接送信されます。 大規模な結果セットの場合、結果セットがクライアントに完全に送信されるまで、ストアド プロシージャ実行は次のステートメントに進みません。 小規模な結果セットの場合、クライアントに返すために結果がスプールされ、実行は続けられます。 ストアド プロシージャの実行中、このような SELECT ステートメントが複数実行された場合、複数の結果セットがクライアントに送信されます。 この動作は、入れ子になっている Transact-SQL バッチ、入れ子になっているストアド プロシージャ、最上位の Transact-SQL バッチにも当てはまります。
結果セットを使用してデータを返す例
次の例では、AdventureWorks2022サンプル データベースを使用します。 この例のストアド プロシージャでは、vEmployee ビューにも表示されるすべての SalesPerson 行に対して LastName 値と SalesYTD 値が返されます。
USE AdventureWorks2022;
GO
IF OBJECT_ID('Sales.uspGetEmployeeSalesYTD', 'P') IS NOT NULL
DROP PROCEDURE Sales.uspGetEmployeeSalesYTD;
GO
CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD
AS
SET NOCOUNT ON;
SELECT LastName, SalesYTD
FROM Sales.SalesPerson AS sp
JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID;
RETURN;
GO
出力パラメーターを使用してデータを返す
プロシージャの定義でパラメーターに OUTPUT キーワードを指定すると、プロシージャの終了時に、そのパラメーターの現在値を呼び出し元のプログラムに返すことができます。 呼び出し元のプログラムで使用できる変数にパラメーターの値を保存するには、呼び出し元のプログラムがプロシージャを実行する際に OUTPUT キーワードを使用する必要があります。 出力パラメーターとして使用できるデータ型の詳細については、「CREATE PROCEDURE (Transact-SQL)」を参照してください。
出力パラメーターの例
次の例では、入力パラメーターと出力パラメーターを使用するプロシージャを示します。 @SalesPerson パラメーターは、呼び出し元のプログラムによって指定された入力値を受け取ります。 SELECT ステートメントは、入力パラメーターに渡された値を使用して、適切な SalesYTD 値を取得します。 また、SELECT ステートメントは、その値を @SalesYTD 出力パラメーターに代入します。これにより、プロシージャの終了時にその値が呼び出し元のプログラムに返されます。
USE AdventureWorks2022;
GO
IF OBJECT_ID('Sales.uspGetEmployeeSalesYTD', 'P') IS NOT NULL
DROP PROCEDURE Sales.uspGetEmployeeSalesYTD;
GO
CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD
@SalesPerson nvarchar(50),
@SalesYTD money OUTPUT
AS
SET NOCOUNT ON;
SELECT @SalesYTD = SalesYTD
FROM Sales.SalesPerson AS sp
JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID
WHERE LastName = @SalesPerson;
RETURN;
GO
次の例では、最初の例で作成したプロシージャを呼び出し、@SalesYTDBySalesPerson 変数に対して呼び出されたプロシージャから返された出力パラメーター @SalesYTD を保存します。
この例では、次の処理を実行します。
- プロシージャの出力値を受け取る
@SalesYTDBySalesPerson変数を宣言します。 - 入力パラメーターの姓を指定する
Sales.uspGetEmployeeSalesYTDプロシージャを実行します。 出力値を変数@SalesYTDBySalesPersonに保存します。 - PRINT を呼び出して、
@SalesYTDBySalesPersonに保存された値を表示します。
DECLARE @SalesYTDBySalesPerson money;
EXECUTE Sales.uspGetEmployeeSalesYTD
N'Blythe', @SalesYTD = @SalesYTDBySalesPerson OUTPUT;
PRINT 'Year-to-date sales for this employee is ' +
CONVERT(varchar(10),@SalesYTDBySalesPerson);
GO
プロシージャを実行するときに、出力パラメーターに対する入力値を指定することもできます。 これにより、プロシージャは、呼び出し元のプログラムから値を受け取って、その値を変更するかその値を使用して操作を実行してから、新しい値を呼び出し元のプログラムに返すことができます。 前の例では、プログラムによって @SalesYTDBySalesPerson プロシージャが呼び出される前に、 Sales.uspGetEmployeeSalesYTD 変数に値を代入できます。 実行ステートメントは、@SalesYTDBySalesPerson 変数の値を @SalesYTD 出力パラメーターに渡します。 その後、プロシージャの本体で、新しい値を生成する計算にその値を使用できます。 新しい値は出力パラメーターを通じてプロシージャから戻され、プロシージャの終了時に @SalesYTDBySalesPerson 変数の値が更新されます。 これは通常、パラメーターの "参照渡し機能" と呼ばれます。
プロシージャを呼び出すときにパラメーターに OUTPUT を指定した場合は、そのパラメーターがプロシージャの定義で OUTPUT を使用して定義されていないと、エラー メッセージが表示されます。 ただし、プロシージャに出力パラメーターを定義しておき、OUTPUT を指定せずにこのプロシージャを実行することも可能です。 エラーは返されませんが、呼び出し側のプログラムで出力値を使用することはできません。
出力パラメーターで cursor データ型を使用する
Transact-SQL プロシージャは、出力パラメーターにのみ cursor データ型を使用できます。 パラメーターに cursor データ型を指定する場合は、プロシージャの定義でそのパラメーターに対して VARYING キーワードと OUTPUT キーワードの両方を指定する必要があります。 ただし、パラメーター宣言で VARYING キーワードを指定する場合は、パラメーターを OUTPUT としてしか指定できません。また、データ型は cursor でなければならず、OUTPUT キーワードも指定する必要があります。
Note
cursor データ型は OLE DB、ODBC、ADO、DB-Library などのデータベース API からアプリケーション変数にバインドすることができません。 アプリケーションでプロシージャを実行するには出力パラメーターがバインドされている必要があるので、cursor 型の出力パラメーターを指定したプロシージャはデータベース API から呼び出すことができません。 そのようなプロシージャは、cursor 型の出力変数を Transact-SQL の cursor 型のローカル変数に代入したときのみ、Transact-SQL のバッチ、プロシージャ、またはトリガーから呼び出すことができます。
cursor 出力パラメーターに関する規則
プロシージャの実行時には、次の規則が cursor 出力パラメーターに適用されます。
順方向専用カーソルの場合、カーソルの結果セットとして返される行は、プロシージャの実行が終了したときにカーソルがあった位置以降の行に限られます。たとえば、次のようになります。
RSという名前の 100 行から構成される結果セットに対するプロシージャ内で、スクロールできないカーソルが開かれます。プロシージャにより結果セット
RSの最初の 5 行がフェッチされます。プロシージャが呼び出し元に戻ります。
呼び出し元に返される結果セット
RSはRSの 6 行目から 100 行目までで構成され、呼び出し元のカーソルはRSの 1 行目の前に置かれます。
順方向専用カーソルの場合、プロシージャが終了した時点でカーソルが 1 行目の前にあれば、呼び出し元のバッチ、プロシージャ、またはトリガーには結果セット全体が返されます。 結果セットが返されるとき、カーソル位置は 1 行目の前に設定されます。
順方向専用カーソルの場合、プロシージャが終了した時点でカーソルが最後の行の後にあれば、呼び出し元のバッチ、プロシージャ、またはトリガーには空の結果セットが返されます。
Note
空の結果セットは、NULL 値と同じではありません。
スクロール可能なカーソルの場合、プロシージャが終了した時点で、呼び出し元のバッチ、プロシージャ、またはトリガーに結果セット内のすべての行が返されます。 結果セットが返されるとき、カーソル位置はプロシージャで最後にフェッチを行った位置のままです。
カーソルがクローズしている場合は、カーソルの種類にかかわらず、呼び出し元のバッチ、プロシージャ、またはトリガーには null 値が返されます。 カーソルがパラメーターに割り当てられていて、そのカーソルが一度も開かれない場合も、同じ結果になります。
Note
カーソルがクローズしているかどうかが問題になるのは、結果セットが返される時点のみです。 たとえば、プロシージャの途中でカーソルを閉じ、その後で再び開いて、そのカーソルの結果セットを呼び出し元のバッチ、プロシージャ、またはトリガーに返すのは有効な操作です。
cursor 出力パラメーターの例
次の例では、カーソル データ型を使用した出力パラメーター @CurrencyCursor を指定したプロシージャを作成します。 作成したプロシージャはバッチで呼び出します。
まず、Currency テーブルに対してカーソルを宣言し、そのカーソルをオープンするプロシージャを作成します。
USE AdventureWorks2022;
GO
IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.uspCurrencyCursor;
GO
CREATE PROCEDURE dbo.uspCurrencyCursor
@CurrencyCursor CURSOR VARYING OUTPUT
AS
SET NOCOUNT ON;
SET @CurrencyCursor = CURSOR FORWARD_ONLY STATIC FOR
SELECT CurrencyCode, Name
FROM Sales.Currency;
OPEN @CurrencyCursor;
GO
次に、cursor 型のローカル変数を宣言し、そのローカル変数にカーソルを代入するプロシージャを実行し、代入したカーソルから行をフェッチするというバッチを実行します。
USE AdventureWorks2022;
GO
DECLARE @MyCursor CURSOR;
EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
FETCH NEXT FROM @MyCursor;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
GO
リターン コードを使用してデータを返す
プロシージャは、リターン コードという整数値を返してプロシージャの実行状態を表すことができます。 プロシージャのリターン コードを指定するには、RETURN ステートメントを使用します。 出力パラメーターと同様に、呼び出し元のプログラムでリターン コード値を使用するには、プロシージャの実行時にリターン コードを変数に保存する必要があります。 たとえば、次のように、int データ型の代入変数 @result を使用して、プロシージャ my_proc からのリターン コードを格納します。
DECLARE @result int;
EXECUTE @result = my_proc;
GO
リターン コードは、可能性のあるエラー状態ごとにリターン コードの値を設定するために、プロシージャのフロー制御ブロックの中でよく使用されます。 Transact-SQL ステートメントの後で @@ERRORを使用すると、ステートメントの実行中にエラーが発生したかどうかを検出できます。 Transact-SQL に TRY/CATCH/THROW エラー処理が導入される前は、ストアド プロシージャの成功と失敗を判断するためにリターン コードが必要になることがありました。 ストアド プロシージャは常に、リターン コードではなく、エラー (必要に応じて、THROW/RAISERROR で生成される) で失敗を示す必要があります。 また、リターン コードでアプリケーション データを返す行為は避けるべきです。
リターン コードの例
次の例では、さまざまなエラーに特別なリターン コード値を設定するエラー処理を含む usp_GetSalesYTD プロシージャを示します。 次の表では、可能性のある各エラーに対してプロシージャによって割り当てられる整数値と、各値に相当する意味を示します。
| リターン コードの値 | 意味 |
|---|---|
| 0 | 実行に成功しました。 |
| 1 | 必要なパラメーター値が指定されていません。 |
| 2 | 指定されたパラメーター値が無効です。 |
| 3 | 売上高の値を取得中にエラーが発生しました。 |
| 4 | 販売員の売上高の値に NULL 値が検出されました。 |
この例では、次のような Sales.usp_GetSalesYTD という名前のプロシージャを作成します。
@SalesPersonパラメーターを宣言し、その既定値をNULLに設定します。 このパラメーターは、営業担当者の姓を取得することを目的としています。@SalesPersonパラメーターを検証します。@SalesPersonが NULL の場合、プロシージャはメッセージを出力し、リターン コード1を返します。@SalesPersonパラメーターが NULL でない場合は、プロシージャは、HumanResources.vEmployeeテーブルで姓が@SalesPersonの値と等しい行の数をチェックします。 カウントがゼロの場合、プロシージャはリターン コード2を返します。
- 指定された姓を持つ販売員の年度累計売上を照会し、それを
@SalesYTD出力パラメーターに割り当てます。 - @@ERROR (Transact-SQL) をテストすることによって、SQL Server エラーをチェックします。
@@ERRORが 0 と等しくない場合、プロシージャはリターン コード3を返します。@@ERRORが 0 と等しい場合、プロシージャは@SalesYTDパラメーター値が NULL かどうかをチェックします。 年度累計売上が見つからなかった場合、プロシージャはリターン コード4を返します。- 上記の条件のいずれも当てはまらない場合、プロシージャはリターン コード
0を返します。
- ストアド プロシージャの最後のステートメントに到達した場合は、入力値を指定せずにストアド プロシージャが再帰的に呼び出されます。
この例の最後には、入力パラメーターに姓を指定し、変数 @SalesYTD に出力値を保存して、Sales.usp_GetSalesYTD プロシージャを実行するコードが用意されています。
USE AdventureWorks2022;
GO
CREATE PROCEDURE Sales.usp_GetSalesYTD
@SalesPerson NVARCHAR(50) = NULL,
@SalesYTD MONEY=NULL OUTPUT
AS
IF @SalesPerson IS NULL
BEGIN
PRINT 'ERROR: You must specify a last name for the sales person.'
RETURN (1)
END
ELSE
BEGIN
IF(SELECT COUNT(*)FROM HumanResources.vEmployee WHERE LastName=@SalesPerson)=0
RETURN (2)
END
SELECT @SalesYTD=SalesYTD
FROM Sales.SalesPerson AS sp
JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID=sp.BusinessEntityID
WHERE LastName=@SalesPerson;
IF @@ERROR<>0
BEGIN
RETURN (3)
END
ELSE
BEGIN
IF @SalesYTD IS NULL
RETURN (4)
ELSE
RETURN (0)
END
EXEC Sales.usp_GetSalesYTD;
GO
DECLARE @SalesYTDForSalesPerson money, @ret_code int;
EXECUTE Sales.usp_GetSalesYTD N'Blythe', @SalesYTD = @SalesYTDForSalesPerson OUTPUT;
PRINT N'Year-to-date sales for this employee is ' +
CONVERT(varchar(10), @SalesYTDForSalesPerson);
GO
次の例では、 usp_GetSalesYTD プロシージャから返されるリターン コードを処理するプログラムを作成します。
この例では、次の処理を実行します。
- プロシージャの出力値とリターン コードを受け取る変数
@SalesYTDForSalesPersonと@ret_codeを宣言します。 @SalesPersonに指定された入力値を使用してSales.usp_GetSalesYTDプロシージャを実行し、出力値とリターン コードを変数に保存します。@ret_codeのリターン コードを確認し、PRINT (Transact-SQL) を呼び出して適切なメッセージを表示します。
DECLARE @SalesYTDForSalesPerson money, @ret_code int;
EXECUTE @ret_code = Sales.usp_GetSalesYTD
N'Blythe', @SalesYTD = @SalesYTDForSalesPerson OUTPUT;
IF @ret_code = 0
BEGIN
PRINT 'Procedure executed successfully';
PRINT 'Year-to-date sales for this employee is ' + CONVERT(varchar(10),@SalesYTDForSalesPerson);
END
ELSE IF @ret_code = 1
PRINT 'ERROR: You must specify a last name for the sales person.';
ELSE IF @ret_code = 2
PRINT 'ERROR: You must enter a valid last name for the sales person.';
ELSE IF @ret_code = 3
PRINT 'ERROR: An error occurred getting sales value.';
ELSE IF @ret_code = 4
PRINT 'ERROR: No sales recorded for this employee.';
GO
関連するコンテンツ
ストアド プロシージャとそれに関連する概念の詳細については、次の記事を参照してください。
フィードバック
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「https://aka.ms/ContentUserFeedback」を参照してください。
フィードバックの送信と表示