ストアド プロシージャからデータを返す
適用対象:SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Microsoft Fabric SQL Database
プロシージャからのデータを呼び出し元のプログラムに返す手段には、結果セット、出力パラメーター、リターン コードの 3 つがあります。 この記事では、3 つのアプローチについて説明します。
この記事の Transact-SQL コード サンプルでは、AdventureWorks2022
または AdventureWorksDW2022
サンプル データベースを使用します。このサンプル データベースは、Microsoft SQL Server サンプルとコミュニティ プロジェクト ホーム ページからダウンロードできます。
結果セットを使用してデータを返す
ストアド プロシージャの本体に SELECT
ステートメント (SELECT ... INTO
や INSERT ... SELECT
は含まない) を含める場合、SELECT
ステートメントで指定された行がクライアントに直接送信されます。 大規模な結果セットの場合、結果セットがクライアントに完全に送信されるまで、ストアド プロシージャ実行は次のステートメントに進みません。 小さな結果セットの場合、結果はクライアントに戻るためにスプールされ、実行が続行されます。 ストアド プロシージャの実行中にこのような SELECT
ステートメントが複数実行されると、複数の結果セットがクライアントに送信されます。 この動作は、入れ子になっている Transact-SQL バッチ、入れ子になっているストアド プロシージャ、最上位の Transact-SQL バッチにも当てはまります。
結果セットを使用してデータを返す例
この例のストアド プロシージャでは、LastName
ビューにも表示されるすべての SalesYTD
行に対して SalesPerson
値と vEmployee
値が返されます。
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
INNER JOIN HumanResources.vEmployee AS e
ON e.BusinessEntityID = sp.BusinessEntityID;
RETURN;
GO
出力パラメーターを使用してデータを返す
プロシージャの定義でパラメーターに OUTPUT キーワードを指定すると、プロシージャの終了時に、そのパラメーターの現在値を呼び出し元のプログラムに返すことができます。 呼び出し元のプログラムで使用できる変数にパラメーターの値を保存するには、呼び出し元のプログラムがプロシージャを実行する際に OUTPUT キーワードを使用する必要があります。 出力パラメーターとして使用できるデータ型の詳細については、「CREATE PROCEDURE
出力パラメーターの例
次の例では、入力パラメーターと出力パラメーターを使用するプロシージャを示します。 @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
INNER JOIN HumanResources.vEmployee AS e
ON e.BusinessEntityID = sp.BusinessEntityID
WHERE LastName = @SalesPerson;
RETURN;
GO
次の例では、最初の例で作成したプロシージャを呼び出し、@SalesYTD
変数に対して呼び出されたプロシージャから返された出力パラメーター @SalesYTDBySalesPerson
を保存します。
この例では、次の処理を実行します。
プロシージャの出力値を受け取る
@SalesYTDBySalesPerson
変数を宣言します。入力パラメーターのファミリ名を指定する
Sales.uspGetEmployeeSalesYTD
プロシージャを実行します。 出力値を変数@SalesYTDBySalesPerson
に保存します。PRINT を呼び出して、
@SalesYTDBySalesPerson
に保存された値を表示します。
DECLARE @SalesYTDBySalesPerson AS 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
OLE DB、ODBC、ADO、DB ライブラリなどのデータベース API を使用して、カーソルデータ型をアプリケーション変数にバインドすることはできません。 アプリケーションでプロシージャを実行する前に出力パラメーターをバインドする必要があるため、カーソル出力パラメーターを持つプロシージャをデータベース 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 AS CURSOR;
EXECUTE dbo.uspCurrencyCursor
@CurrencyCursor = @MyCursor OUTPUT;
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM @MyCursor;
END
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
GO
リターン コードを使用してデータを返す
プロシージャは、リターン コードという整数値を返してプロシージャの実行状態を表すことができます。 RETURNを使用して、プロシージャのリターン コードを指定します。 出力パラメーターと同様に、呼び出し元のプログラムでリターン コード値を使用するには、プロシージャの実行時にリターン コードを変数に保存する必要があります。 たとえば、int
DECLARE @result AS 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
されていない場合、プロシージャは、@SalesPerson
の値と等しいファミリ名を持つHumanResources.vEmployee
テーブル内の行数をチェックします。 カウントがゼロの場合、プロシージャはリターン コード2
を返します。
指定したファミリ名を持つ営業担当者の年累計売上を照会し、
@SalesYTD
出力パラメーターに割り当てます。@@ERRORをテストして 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
INNER 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
EXECUTE Sales.usp_GetSalesYTD;
GO
DECLARE @SalesYTDForSalesPerson AS MONEY, @ret_code AS 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
を宣言します。Sales.usp_GetSalesYTD
に指定された入力値を使用して@SalesPerson
プロシージャを実行し、出力値とリターン コードを変数に保存します。のリターン コードを確認し、PRINT 呼び出して適切なメッセージを表示します。
DECLARE @SalesYTDForSalesPerson AS MONEY, @ret_code AS 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