SQL Server: 結果セット、テーブル変数、戻り値ステータスコード… ストアド プロシージャのデータ返却方法徹底比較
2024-07-27
SQL Server ストアド プロシージャにおけるオプションの OUTPUT パラメータ
OUTPUT パラメータとは？
OUTPUT パラメータは、ストアド プロシージャの実行時に値を呼び出し元に返すことができる特殊なパラメーターです。入力パラメータとは異なり、OUTPUT パラメータはプロシージャの実行前に値を設定する必要はありません。
オプションの OUTPUT パラメータを使用する利点
- コードの簡潔化: 複雑な条件分岐を使用して値を返す必要がなくなります。
- エラー処理: エラーが発生した場合に、NULL 値を返すことでエラーを通知できます。
- 柔軟性: 常に値を返す必要がないプロシージャを作成できます。
オプションの OUTPUT パラメータを定義するには、CREATE PROCEDURE ステートメントで OUTPUT キーワードを使用します。
CREATE PROCEDURE MyProcedure
(
@InputParameter1 data_type,
@OptionalOutputParameter data_type OUTPUT
);
この例では、@OptionalOutputParameter はオプションの OUTPUT パラメータです。
- ストアド プロシージャを定義します。
- プロシージャを実行します。
- OUTPUT パラメータの値を取得します。
DECLARE @OutputValue data_type;
EXEC MyProcedure
@InputParameter1 = 'value1',
@OptionalOutputParameter = @OutputValue OUTPUT;
SELECT @OutputValue;
この例では、@OutputValue 変数は MyProcedure プロシージャの @OptionalOutputParameter パラメータの値を受信します。
注意事項
- オプションの OUTPUT パラメータは、プロシージャの最初の OUTPUT パラメータである必要があります。
- オプションの OUTPUT パラメータは、スカラ値のみをサポートします。
CREATE PROCEDURE GetCustomerInfo
(
@CustomerID INT,
@CustomerName NVARCHAR(128) OUTPUT,
@PostalCode NVARCHAR(16) OUTPUT
);
AS
BEGIN
DECLARE @CustomerNameValue NVARCHAR(128);
DECLARE @PostalCodeValue NVARCHAR(16);
SELECT
@CustomerNameValue = CustomerName,
@PostalCodeValue = PostalCode
FROM Customers
WHERE CustomerID = @CustomerID;
SET @CustomerName = @CustomerNameValue;
SET @PostalCode = @PostalCodeValue;
END;
このプロシージャを呼び出すには、次のステートメントを使用します。
DECLARE @CustomerName NVARCHAR(128);
DECLARE @PostalCode NVARCHAR(16);
EXEC GetCustomerInfo
@CustomerID = 123,
@CustomerName = @CustomerName OUTPUT,
@PostalCode = @PostalCode OUTPUT;
SELECT @CustomerName, @PostalCode;
このコードを実行すると、@CustomerName 変数には顧客の名前、@PostalCode 変数には顧客の郵便番号が格納されます。
顧客情報の更新
このストアド プロシージャを更新して、顧客の名前と郵便番号を更新できるようにすることもできます。
CREATE PROCEDURE UpdateCustomerInfo
(
@CustomerID INT,
@NewCustomerName NVARCHAR(128),
@NewPostalCode NVARCHAR(16)
);
AS
BEGIN
UPDATE Customers
SET CustomerName = @NewCustomerName,
PostalCode = @NewPostalCode
WHERE CustomerID = @CustomerID;
END;
EXEC UpdateCustomerInfo
@CustomerID = 123,
@NewCustomerName = 'John Doe',
@NewPostalCode = '98123';
結果セットの返却
最も単純な方法は、プロシージャが結果セットを返し、呼び出し元でその結果セットを処理することです。
CREATE PROCEDURE GetCustomerInfo
(
@CustomerID INT
);
AS
BEGIN
SELECT CustomerName, PostalCode
FROM Customers
WHERE CustomerID = @CustomerID;
END;
DECLARE @CustomerName NVARCHAR(128);
DECLARE @PostalCode NVARCHAR(16);
SELECT @CustomerName = CustomerName,
@PostalCode = PostalCode
FROM GetCustomerInfo(@CustomerID = 123);
SELECT @CustomerName, @PostalCode;
テーブル変数の使用
テーブル変数は、プロシージャ内で一時的なデータ構造を作成するために使用できる特別な変数です。
CREATE PROCEDURE GetCustomerInfo
(
@CustomerID INT,
@CustomerTable TABLE OUTPUT
);
AS
BEGIN
INSERT INTO @CustomerTable
(
CustomerName,
PostalCode
)
SELECT CustomerName, PostalCode
FROM Customers
WHERE CustomerID = @CustomerID;
END;
DECLARE @CustomerTable TABLE (
CustomerName NVARCHAR(128),
PostalCode NVARCHAR(16)
);
EXEC GetCustomerInfo
@CustomerID = 123,
@CustomerTable = @CustomerTable OUTPUT;
SELECT * FROM @CustomerTable;
このコードを実行すると、@CustomerTable 変数には顧客の情報を含むテーブルが格納されます。
戻り値のステータス コードの使用
プロシージャは、成功または失敗を示すステータス コードを返すことができます。
CREATE PROCEDURE UpdateCustomerInfo
(
@CustomerID INT,
@NewCustomerName NVARCHAR(128),
@NewPostalCode NVARCHAR(16),
@Status INT OUTPUT
);
AS
BEGIN
UPDATE Customers
SET CustomerName = @NewCustomerName,
PostalCode = @NewPostalCode
WHERE CustomerID = @CustomerID;
IF @@ERROR <> 0
BEGIN
SET @Status = 1; -- エラーが発生しました
END
ELSE
BEGIN
SET @Status = 0; -- 成功しました
END;
END;
DECLARE @Status INT;
EXEC UpdateCustomerInfo
@CustomerID = 123,
@NewCustomerName = 'John Doe',
@NewPostalCode = '98123',
@Status = @Status OUTPUT;
SELECT @Status;
このコードを実行すると、@Status 変数には、更新操作が成功したかどうかを示す値が格納されます。
適切な方法の選択
使用する方法は、特定のニーズによって異なります。
- 戻り値のステータス コードは、操作の成否を通知する場合に適しています。
- テーブル変数は、より複雑なデータ構造を返す場合に適しています。
- 結果セットを返す方法は、単純なデータの取得に適しています。
sql-server stored-procedures data-access
