SqlParameter クラスを利用してクエリをパラメータ化すると sp_executesql に変換されて SQL Server で実行されるのですが、変換によって予期せぬ問題が出ることがあるという話を書きます。
元の話は MSDN Forum のスレッド「パラメータを利用したLikeで欲しい結果が得られない 」です。
具体的にどういうことかは以下の通りです。
SQL Server のデータベースに以下の画像の通り tbl_Item テーブルがあり、Item_CD 列で LIKE 句による検索をかけるとします。型の nvarchar(6) を覚えておいてください。
この tbl_Item テーブルをベースに、Visual Studio のデーターソース構成ウィザードで型付 DataSet + TableAdapter を作成します。Item_CD 列で検索できる TableAdapter のメソッドを追加します。その SELECT クエリは以下の画像の通りです。
上の画像の通り型付 DataSet + TableAdapter が作成できたら「データのプレビュー」画面を開きバラメータ ICD に %A00001% を代入して［プレビュー］ボタンをクリックします。3 レコードヒットすると期待しますが、結果は一件もヒットしません。
ICD に代入した %A00001% を %00001% に代えた場合は 3 件のレコードがヒットします。上に紹介した MSDN Forum のスレッドの最初の質問に書いてあった通りです。
何故 %A00001% では一件もヒットしないかですが、上に紹介した MSDN Forum のスレッドで質問者さんがアップしてくれたプロファイラの画像を見ると分かるので、借用して下に貼っておきます。
理由は Item_CD 列の型 nvarchar(6) の 6 で制限を受けるからです。具体的には以下の 2 つです。
(1) SqlParameter の Size
上の画像の通り SELECT クエリをベースに Visual Studio のデータソース構成ウィザードで FillByICD, GetDataByICD メソッドを生成していますが、その際 @ICD には SqlParameter が以下のように初期化されパラメータとして設定されます。
this._commandCollection[1].Parameters.Add(
new SqlParameter("@ICD", SqlDbType.NVarChar, 6,
ParameterDirection.Input, 0, 0, "Item_CD",
DataRowVersion.Current, false, null, "", "", ""));
SqlParameter コンストラクタの第 3 引数がデータベースの Item_CD 列の型 nvarchar(6) の 6 となり、結果 SQL Server に送信するデータの最大量が 6 文字に制限されます。
上のプロファイラの画像で @CID=N'%A0000' と 6 文字になっているところに注目してください。この時は C# のコードでは "%A00001%" という文字列を代入したとのことですが、SQL Server では先頭から 6 文字だけしか受け取れていません。
(2) sp_executesql の N'@ICD nvarchar(6)'
SqlParameter を使った ADO.NET のパラメータ化クエリは、上のプロファイラ画像のとおり sp_executesql に変換されます。その中の N'@ICD nvarchar(6)' の 6 でも制限を受けます。
一番上の画像を見てください。SSMS で sp_executesql を実行したもので、そこでは @CID=N'%A00001%' としていますが、先頭から 6 文字しか SQL Server には渡せないようで、一件もヒットしていません。(@ICD には %A0000 が代入されると思われる)
ちなみに、N'@ICD nvarchar(6)', の 6 を 7 に変えると同じく @CID=N'%A00001%' で 3 件ヒットします。(@ICD には %A00001 が代入されると思われる)
何が N'@ICD nvarchar(6)', の 6 を決めているのかという疑問は残っていますが、この 6 が制限の一つであるのは結果から間違いなさそうです。
回避策は WHERE 句を WHERE Item_CD LIKE N'%' + @ICD + N'%' のように変更して、@ICD には A00001 と入力するのがよさそうです。
と言うか、普通は最初から WHERE Item_CD LIKE N'%' + @ICD + N'%' とすると思います。だから、今回の問題のような話は初耳だったのかもしれません。
LIKE 句を使った時の問題は、自分が知る限りですが先の記事「固定長パラメータの LIKE 比較」に書いた話がありました。
加えて、LIKE 句を使うと今回のような問題も起こり得るということを知ることができて、MSDN Forum のおかげで一つ利口になったような気がします。