次の方法で共有


ストアド プロシージャからデータを返す

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsMicrosoft Fabric SQL Database

プロシージャからのデータを呼び出し元のプログラムに返す手段には、結果セット、出力パラメーター、リターン コードの 3 つがあります。 この記事では、3 つのアプローチについて説明します。

この記事の Transact-SQL コード サンプルでは、AdventureWorks2022 または AdventureWorksDW2022 サンプル データベースを使用します。このサンプル データベースは、Microsoft SQL Server サンプルとコミュニティ プロジェクト ホーム ページからダウンロードできます。

結果セットを使用してデータを返す

ストアド プロシージャの本体に SELECT ステートメント (SELECT ... INTOINSERT ... 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 行がフェッチされます。

    • プロシージャが呼び出し元に戻ります。

    • 呼び出し元に返される結果セット RSRS の 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 パラメーターを検証します。

    • @SalesPersonNULL場合、プロシージャはメッセージを出力し、戻りコード 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