■要約

SQL Server上で動作するストアドプロシージャの単体テストをVisual Studioで行うための環境構築手順及び実施手順をまとめた

■0.はじめに

恐らく需要が少ないからだとは思うんですが、SQL Serverのストアドプロシージャに関する日本語の情報ってあまりなくて、さらに単体テストになると公式以外にはほとんど見かけなくて、結構苦労しました。
今後同じ苦労をするであろう人の最初の取っ掛かりになればという思いで、環境構築手順、実施手順をまとめました。

■1.環境

Windows 10
Microsoft Visual Studio Professional 2019

■2.環境構築

▼2.1.前提条件

SQL Server Data ToolsをVisual Studioにインストールしていること
　(参考：https://docs.microsoft.com/ja-jp/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-2017)
単体テストを行うデータベースのデータ層アプリケーションファイル(.dacpac)が用意されていること
SQL Server Management Studioがインストールされていること
　(参考：https://docs.microsoft.com/ja-jp/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15)

▼2.2.構築手順

プロジェクトの作成
- 新しいプロジェクトの作成から、[SQL Server データベースプロジェクト]を選択して次へ(クエリ言語で絞り込むとよい)
- 次の画面でプロジェクト名などを入力して作成
テーブル情報の読み込み
- ソリューションエクスプローラー上から作成したプロジェクトを右クリック-[インポート]-[データ層アプリケーション]を選択
- 事前に準備しておいた、単体テストを実施するストアドプロシージャが動作するDBのdacpacを選択する
データベースの設定を変更する
- ソリューションエクスプローラー上からプロジェクトを右クリック-[プロパティ]を選択
- [プロジェクトの設定]タブから右下にある[データベースの設定]を選択し、以下の通り設定
単体テスト対象のストアドプロシージャを読み込む
- ソリューションエクスプローラー上からプロジェクトを右クリック-[インポート]-[スクリプト]を選択し、ストアドプロシージャを読み込む
- ストアドプロシージャの先頭はCREATE PROCEDUREになっている必要がある
- エンコードはDBの設定に合わせたものにする
ローカルDBを用意する
- ソリューションエクスプローラー上からプロジェクトを選択した状態でF5キーを押下する
- この操作でここまでに作成したDB環境が作成されているので、SQL Serverオブジェクトエクスプローラーを開き、テーブルやストアドプロシージャが登録されているのを確認する
- 単体テストを実施するストアドプロシージャが動作するDBについて、データ保管用のDBをローカルDB上に作成しておくと、なおテストが行いやすくなる
単体テストを作成する
- ソリューションエクスプローラー上から対象のストアドプロシージャを右クリック-[単体テストの作成]を選択する
- 単体テストは別のプロジェクトとして作成されるので、新しいプロジェクト名と出力クラスを入力する
- 作成したプロジェクトをソリューションエクスプローラー上から右クリック-[SQL Serverテスト構成]を選択する
- [接続の選択]を選択し、[参照タブ]-[ローカル]から作成したDBを選択する
テスト実施の為の前準備
- テスト対象のストアドプロシージャからトランザクションの開始/終了/破棄の呼び出しをコメントアウトする
  - BEGIN TRANSACTION
  - COMMIT TRANSACTION
  - ROLLBACK TRANSACTION

■3.単体テストを行うまでの流れ

▼3.1.実施イメージ

▼3.2.基本的な流れ

！同じテストケースを何度実行しても同じ結果が得られるよう、テストコードを書くこと！

以下にそれに沿ったテストコードの記載手順を記す。

1.テストに必要なデータを必要なテーブルに投入する

ストアドプロシージャの実行前に、テストに必要なデータを作成もしくは他の環境(ローカルDB)からコピーする。
1つのテストケースだけで使用するデータであれば、そのテストケースの実行前のみ実行される[事前テスト]にSQL文を記載し、
他のテストケースでも使用する共通したデータであれば、各テストケースの実行前に実行される[共通スクリプト]-[テストの初期化]にSQL文を記載する。

例1.sql

-- 変数定義	
DECLARE 	
	@Target_Id AS bigint 
	;

-- 変数の初期値設定	
SELECT @Target_Id = 3887613;
	
-- 必要なデータのセットアップを行う
-- リンクサーバー経由でローカルDBに保管しておいたデータをINSERTする
INSERT Input_Data
SELECT * FROM LINK1.TargetDB.dbo.Input_Data
WHERE Id = @Target_Id
;

2.ストアドプロシージャを実行し、想定通りの結果となっているかを評価する

Visual Studio上で単体テストケースを作成すると、該当のストアドプロシージャの呼び出しのみ書かれたコードが生成されるので、
そこに実行後に評価できるようコードを書いていく。

例2.sql

-- 変数定義
DECLARE 
	@RC AS INT
	,@prmExecuteTime AS VARCHAR (19)
	,@Target_Id AS bigint
	;

-- 変数の初期値設定
SELECT @RC = 99,
       @prmExecuteTime = '2019-01-04 16:00:00'
	   ,@Target_Id = 3887613
	   ;

-- ストアド実行
EXECUTE @RC = [dbo].[UpdateTargetData] @prmExecuteTime;

-- *** 評価 *** --
-- 1.戻り値が0であること(=正常終了)
-- ResultSet 1
SELECT @RC AS RC;

-- 2.修正内容評価1
-- テーブルが仕様通りに更新されていること
-- 1件だけ存在していること
-- ResultSet 2
SELECT * FROM Target_Table WHERE Id = @Target_Id;

-- 作成されたデータと想定結果をEXCEPTして、1行も返ってこなければ完全に一致(=評価OK)となる
-- ResultSet 3
SELECT 
	Id
	,Start_Date
	,End_Date
FROM 
	Target_Table WHERE Id = @Target_Id
EXCEPT
SELECT
	@Target_Id 
	,'2019-01-01'
	,'2019-01-31'
;

(他の方法もあるが)基本的にはテストコード上に記載されたSELECT ～の戻り値を1つのResultSetとして、それの値について
GUI上でOK/NGの判断条件を設定していく

複数の項目値を評価したい場合は、上記例のResultSet3のようにEXCEPTを使用し、
作成されたデータと想定データが同一かを評価するとよい

-- 作成されたデータと想定結果をEXCEPTして、1行も返ってこなければ完全に一致(=評価OK)となる
-- ResultSet 3
SELECT 
	Id
	,Start_Date
	,End_Date
FROM 
	Target_Table WHERE Id = @Target_Id
EXCEPT
SELECT
	@Target_Id 
	,'2019-01-01'
	,'2019-01-31'
;

3.データをクリアする

1.で投入したデータ、2.で作成されたデータをクリアする

例3.sql

-- 変数定義	
DECLARE
	@Target_Id AS bigint 
	;

-- 変数の初期値設定	
SELECT @Target_Id = 3887613;

-- 事前テストで作成したデータを削除する
DELETE FROM Input_Data WHERE Id = @Target_Id;

▼3.3.応用編

- IDENTITY(自動採番)の項目があるテーブルのデータをコピーする場合

IDENTITY(自動採番)の項目があるテーブルのデータについて、IDENTITY(自動採番)の項目の値を保ったままINSERTするには一工夫が必要になる。
具体的には、デフォルトではOFFに設定されているIDENTITY_INSERTプロパティを一時的にONにしてからINSERTすることで
IDENTITY(自動採番)の項目の値を保ったままINSERTすることができる

例1.sql

-- Idが自動採番なので、元々の値を維持したままコピーするために一時的にON
SET IDENTITY_INSERT Input_Data ON;
-- 自動採番の項目を含む場合はカラム指定する必要があるよう
INSERT INTO Input_Data(
	Id
	,Column_A
	,Column_B
	,Column_C
)
SELECT * FROM LINK1.TargetDB.dbo.Input_Data
;

- テスト時に単体テスト対象のストアドとは別DBのデータを事前に変更(INSERT/UPDATE/DELETE)する必要がある場合

テストコード内では別DBのデータを追加・変更・削除することはできない(エラーになる)
できるだけこういうケースが発生しないようローカルDBを整備しておくことが望ましいが、
どうしても必要がある場合は、テスト前/後に流すSQLを用意し、それぞれSSMSで実行する手順としておく。

例2_事前実行.sql

-- 事前バックアップ
SELECT * INTO Monthly_Setting_Master_UT FROM Monthly_Setting_Master;

-- データ変更
UPDATE Monthly_Setting_Master SET Execute_Time='2018-12-31 05:00:00.000', Search_Month=201812 WHERE Id=276816;
UPDATE Monthly_Setting_Master SET Execute_Time='2018-12-31 15:00:00.000', Search_Month=201812 WHERE Id=11311;

例2_事後実行.sql

-- データクリア
TRUNCATE TABLE Monthly_Setting_Master;

-- バックアップからデータを戻す
INSERT INTO Monthly_Setting_Master SELECT * FROM Monthly_Setting_Master_UT;

-- バックアップを削除
DROP TABLE Monthly_Setting_Master_UT;

上記例のように、テスト前に変更するテーブル全件を別テーブルに退避した上で変更し、
テスト後に退避しておいた別テーブルで全データ差し替え、退避に使った別テーブルを削除するのが簡単

■参考資料

チュートリアル:SQL Server の単体テストの作成と実行 - SQL Server | Microsoft Docs