【SQL Server】ストアドプロシージャの使い方 / C#から実行する方法

SQL Serverにおけるストアドプロシージャについて概要やC#(Dapper)での使い方などをまとめます。

※この記事ではSQL Server と C#の組み合わせで検証します。RDMSによっては仕様や概念が異なる場合があるためご注意ください。

ストアドプロシージャとは

アプリケーションをクライアントで実行する際に、サーバとの間でSQLの送信と実行結果の受信が何度も繰り返されます。これだと通信量が増えてしまいパフォーマンスの低下につながります。

ストアドプロシージャは、一度の呼び出しで複数のSQLをまとめて処理することができます。データベース上に保存しておき、クライアントがそれを呼び出す形で使用します。

ストアドプロシージャのメリット

パフォーマンスの向上

繰り返し使用する定型的な複数のSQLをストアドプロシージャとしてひとまとめにすることで、通信回数を減らしサーバへの負荷を減らします。

ロジックの共有

ストアドプロシージャはデータベースに保存するため、別のアプリケーションから呼び出すことも可能です。同様の処理を行う別のアプリケーションでも共有することができます。

セキュリティ強化

ユーザの権限をストアドプロシージャ経由でしか操作できないように設定することで、テーブルに対する不要な操作を防ぐことが可能になります。直接操作をさせずにストアドプロシージャ経由でしか操作させないことによりセキュリティを強化できます。

ストアドプロシージャの使い方(SQL Server)

下記Personテーブルを使って試してみます

作成 (CREATE PROCEDURE)

CREATE PROCEDURE SampleProcedure
 AS
	SELECT * FROM Person WHERE BloodTypeID = 1;

パラメータ付

パラメータ（引数）を設定することも可能です。「@」を先頭につけた名前の後にデータ型を指定します。

複数のパラメータを指定する場合はカンマ区切りで指定します。

CREATE PROCEDURE SampleProcedure2
 @ageFrom int, 
 @bloodType int
 AS
	SELECT * FROM Person WHERE Age >= @ageFrom AND BloodTypeID = @bloodType;

初期値(デフォルト引数)を指定したい場合はデータ型の後に「=」で設定します

CREATE PROCEDURE SampleProcedure2
 @ageFrom int  = 20, 
 @bloodType int = 1
 AS
	SELECT * FROM Person WHERE Age >= @ageFrom AND BloodTypeID = @bloodType;

実行 (EXEC)

実行する場合は「EXEC」の後にストアドプロシージャ名を指定します。

パラメータ（引数）はストアドプロシージャ名の後に指定します。

EXEC SampleProcedure;

--パラメータ付き(どちらでもOK) 
EXEC SampleProcedure2 30, 1;
EXEC SampleProcedure2 @ageFrom=30, @bloodType=1;

変更 (ALTER PROCEDURE)

ストアドプロシージャの内容を変更したい場合は「ALTER PROCEDURE」を使います

ALTER PROCEDURE SampleProcedure2
 @name nvarchar(10)
 AS
	SELECT * FROM Person WHERE Name = @name

削除 (DROP PROCEDURE)

DROP PROCEDURE SampleProcedure2

戻り値の設定

戻り値を設定したい場合は「OUTPUT」を下記のようにパラメータとして定義します

SET もしくは SELECT で戻り値に指定したパラメータに値を格納します

CREATE PROCEDURE SampleProcedure2
 @bloodType int,
 @recordNum int OUTPUT
 AS
	SELECT * FROM Person WHERE  BloodTypeID = @bloodType
	SET @recordNum = @@ROWCOUNT		-- SELECT @recordNum = @@ROWCOUNT

-- @@ROWCOUNT は処理レコード数を取得します

戻り値を受け取る際は、変数を宣言しておきストアドプロシージャにパラメータとして渡します。そうすると変数に戻り値が格納されます。

DECLARE @recorNum int;
EXEC SampleProcedure2 1, @recorNum OUTPUT;

SELECT @recorNum;

C#(Dapper)でストアドプロシージャを使う

Dapperでストアドプロシージャを使う場合に、通常とは少し書き方が異なります。

using Dapper;
using System.Data.SqlClient;

var connectionString = @"Data Source=(localdb)ProjectsV13;Initial Catalog=TEST;Integrated Security=True";
using(var con = new SqlConnection(connectionString))
{
    con.Open();

    // ストアドプロシージャ名
    var storedProcedure = "SampleProcedure2";
    
    // パラメータ
    var param = new DynamicParameters();
    
    // ストアドプロシージャに渡すパラメータ
    param.AddDynamicParams(new { bloodType = 1 });
    
    // OUTPUT（戻り値のパラメータ）
    param.Add("@recordNum", dbType: System.Data.DbType.Int32, direction: System.Data.ParameterDirection.Output);
    
    // ストアドプロシージャの実行
    var result = con.Execute(storedProcedure, param, commandType: System.Data.CommandType.StoredProcedure);
    
    // クラスにマッピングしたい場合はQuery<T>を使用
    //var result = con.Query<Person>(storedProcedure, p,  commandType: System.Data.CommandType.StoredProcedure);

    // OUTPUTの値の取得
    Console.WriteLine(param.Get<int>("@recordNum"));
}

Csharp