SQLServer where句をある条件のときだけ有効にする方法

Transact-SQL で、どうしてもコードで SQL を生成したくないときに使えるテクニック。

例として、何か値を単純に判定するケースと、日付を範囲で判定するケースを書いてみました。

coalesce を使って、値が NULL じゃないときだけ、条件を有効にする

where
	1 = 1
	and coalesce(COL_1,0) = coalesce(@VAL_1,COL_1,0)
	and coalesce(COL_2,0) between
		coalesce(@VAL_START_DATE,COL_2)
		and
		coalesce(@VAL_END_DATE,COL_2)

case を使って、値が NULL じゃないときだけ、条件を有効にする

case を使えば、NULL 判定以外も可能です。

where
	1 = 1
	and COL_1 = case when @VAL_1 is null then COL_1 else @VAL_1 end
	and COL_2 between coalesce(@VAL_START_DATE,COL_2) and coalesce(@VAL_END_DATE,COL_2)

注意事項

どうしても、コードで SQL を生成したくないときや、SQL 単体でどうにかしたいとき以外は使わないの方が良いかもしれません。

インデックスがうまく使えなかったりと、パフォーマンス的に不利になるケースがあります。