Entity Frameworkで出力パラメータを持つストアドプロシージャを簡単に実行

2024-04-02

SQL Serverで出力パラメータを持つストアドプロシージャを実行する方法

出力パラメータを持つストアドプロシージャの作成

SQL Server Management Studio (SSMS) を開きます。
対象となるデータベースに接続します。
オブジェクトエクスプローラーで ストアドプロシージャ フォルダを展開します。
右クリックして 新しいストアドプロシージャ を選択します。
ストアドプロシージャの名前を入力します (例: GetCustomerCount)。
作成をクリックします。
ストアドプロシージャのコードエディタが表示されます。
以下のコード例を参考に、出力パラメータと処理内容を記述します。

USE [AdventureWorks2019]
GO

CREATE PROCEDURE [dbo].[GetCustomerCount]
	@OutputCustomerCount INT OUTPUT
AS
BEGIN
	SELECT @OutputCustomerCount = COUNT(*)
	FROM [Person].[Customer]
END
GO

この例では、@OutputCustomerCount という名前の出力パラメータを作成し、Person.Customer テーブルの顧客数を格納しています。

出力パラメータを持つストアドプロシージャの実行

SSMS で クエリエディタ を開きます。
以下のコード例を参考に、ストアドプロシージャを実行します。

DECLARE @CustomerCount INT

EXEC [dbo].[GetCustomerCount] @OutputCustomerCount OUTPUT

SELECT @CustomerCount

この例では、@CustomerCount という変数を宣言し、GetCustomerCount ストアドプロシージャを実行して出力パラメータの値を取得しています。

その他の注意事項

出力パラメータは、必ず OUTPUT キーワードで宣言する必要があります。
出力パラメータは、ストアドプロシージャ内で値が割り当てられる必要があります。
出力パラメータの値は、呼び出し元で変数に格納することができます。
出力パラメータは、複数のデータ型をサポートしています (例: INT, VARCHAR, DATETIME など)。

USE [AdventureWorks2019]
GO

CREATE PROCEDURE [dbo].[GetCustomerCount]
	@OutputCustomerCount INT OUTPUT
AS
BEGIN
	SELECT @OutputCustomerCount = COUNT(*)
	FROM [Person].[Customer]
END
GO

呼び出しコード

DECLARE @CustomerCount INT

EXEC [dbo].[GetCustomerCount] @OutputCustomerCount OUTPUT

SELECT @CustomerCount

出力

このサンプルコードでは、GetCustomerCount ストアドプロシージャを作成して、Person.Customer テーブルの顧客数を取得しています。出力パラメータ @OutputCustomerCount は、呼び出し元で変数 @CustomerCount に格納され、結果として 12648 という値が出力されます。

顧客情報の取得:

USE [AdventureWorks2019]
GO

CREATE PROCEDURE [dbo].[GetCustomerInfo]
	@CustomerID INT,
	@OutputFirstName VARCHAR(50) OUTPUT,
	@OutputLastName VARCHAR(50) OUTPUT
AS
BEGIN
	SELECT @OutputFirstName = FirstName,
		   @OutputLastName = LastName
	FROM [Person].[Customer]
	WHERE CustomerID = @CustomerID
END
GO

DECLARE @FirstName VARCHAR(50),
		@LastName VARCHAR(50)

EXEC [dbo].[GetCustomerInfo] 1234, @OutputFirstName OUTPUT, @OutputLastName OUTPUT

SELECT @FirstName, @LastName

製品情報の更新:

USE [AdventureWorks2019]
GO

CREATE PROCEDURE [dbo].[UpdateProductPrice]
	@ProductID INT,
	@NewPrice MONEY,
	@OutputRowsAffected INT OUTPUT
AS
BEGIN
	UPDATE [Production].[Product]
	SET StandardCost = @NewPrice
	WHERE ProductID = @ProductID

	SET @OutputRowsAffected = @@ROWCOUNT
END
GO

DECLARE @RowsAffected INT

EXEC [dbo].[UpdateProductPrice] 789, 100.00, @OutputRowsAffected OUTPUT

SELECT @RowsAffected

これらのサンプルコードは、出力パラメータを持つストアドプロシージャの使用方法を理解するのに役立ちます。

出力パラメータを持つストアドプロシージャを実行する他の方法

Transact-SQL (T-SQL) コマンド

EXEC [dbo].[GetCustomerCount] @OutputCustomerCount OUTPUT

SELECT @OutputCustomerCount

ADO.NET

using (var connection = new SqlConnection("..."))
{
    var cmd = new SqlCommand("[dbo].[GetCustomerCount]", connection);
    cmd.CommandType = CommandType.StoredProcedure;

    var outputParameter = new SqlParameter("@OutputCustomerCount", SqlDbType.Int);
    outputParameter.Direction = ParameterDirection.Output;
    cmd.Parameters.Add(outputParameter);

    cmd.ExecuteNonQuery();

    var customerCount = (int)outputParameter.Value;
}

Entity Framework

using (var context = new AdventureWorks2019Context())
{
    var customerCount = context.Database.SqlQuery<int>("EXEC [dbo].[GetCustomerCount] @OutputCustomerCount OUTPUT",
        new SqlParameter("@OutputCustomerCount", SqlDbType.Int) { Direction = ParameterDirection.Output });
}

その他の言語

上記以外にも、Python、Java、JavaScript などの様々な言語から出力パラメータを持つストアドプロシージャを実行することができます。それぞれの言語に合ったライブラリやツールを使用する必要があります。

どの方法を選択するべきかは、開発環境や要件によって異なります。以下は、それぞれの方法の利点と欠点です。

SSMS