SQLServerのVARCHAR型変数を条件式に組み込む際の注意点
VARCHAR型は宣言時に桁数を指定できるが省略もできる
MAX指定もできますね。
declare @str_omt varchar
, @str_255 varchar(255)
, @str_max varchar(max)
変数の初期値はNULLになります。
select @str_omt [@str_omt]
, @str_255 [@str_255]
, @str_max [@str_max]
| @str_omt | @str_255 | @str_max |
|---|---|---|
| NULL | NULL | NULL |
省略時どうなってるかというと、varchar(1)と等しいです。
set @str_omt = 'string'
set @str_255 = 'string'
set @str_max = 'string'
| @str_omt | @str_255 | @str_max |
|---|---|---|
| s | string | string |
IsNull関数で代入した場合
ここから本題、IsNullでさっくり分岐させたいなーなどと。
NULL時に'null string'と表示させたいのでそんなSQLを書いてみると・・・？
set @str_omt = null
set @str_255 = null
set @str_max = null
select IsNull(@str_omt,'null string') [@str_omt]
, IsNull(@str_255,'null string') [@str_255]
, IsNull(@str_max,'null string') [@str_max]
結果
| @str_omt | @str_255 | @str_max |
|---|---|---|
| n | null string | null string |
varchar型変数をIsNullで分岐させた場合、表示はあくまでもその変数の桁数しかセットされません。
WHERE条件でCOUNTしてみると？
ただSELECT文で実行するだけならレコード数は変わりません。
これがWHERE条件などに組み込んでくるとレコード数が変わり被害が拡大します。
select * from
(select count(*) count_omt where 'null string' = isnull(@str_omt, 'null string')) a
,(select count(*) count_255 where 'null string' = isnull(@str_255, 'null string')) b
,(select count(*) count_max where 'null string' = isnull(@str_max, 'null string')) c
結果
| count_omt | count_255 | count_max |
|---|---|---|
| 0 | 1 | 1 |
何が原因かというと、
'null string' = isnull(@str_omt, 'null string')
の部分の評価式が
'null sgtring' = 'n'
となってしまうためです。
こんなSQLは特に注意です。
declare @id varchar(5)
select a.id ,b.name
from トランザクション a
inner join マスターテーブル b
on a.id = b.id
where a.id = IsNull(@id, a.id)
@idがNULLの場合、全件取得したいのですが、
もしDB設計変更でSQLだけ修正漏れがあった場合、
トランザクションから全件取得するつもりがidが5桁までのレコードしか取得できません。
ところで、C#のSqlDbType.VarCharあるじゃない？
ありますね。
上記公式のドキュメントのVarChar型の説明にはこう書かれてます。
データベース列が varchar(max) の場合、VarChar を使用します。
C#側でコーディングする際にパラメータをSqlDbType.VarCharとするなら問題はなさそうですね。
SQL文に直接declareで変数宣言する場合は気を付けたほうが良さそうですけど、
まぁインジェクション対策にできるだけコマンドパラメータ使用したほうがいいと思います。
SqlCommand cmd = new SqlCommand();
string s = @"select @str_max [@str_max]"
cmd.CommandText = s;
cmd.Parameters.Add("@str_max", SqlDbType.VarChar).Value = "keyword";
まとめ
SQLServerでvarchar型を宣言する際に桁数を気を付けよう。
容量が気にならない場合はMAX、それ以外は業務で十分に確保できる桁数を指定。
安易に桁数を省略しない、むしろ省略は使わない。
DB設計は後からの変更は地獄なのでよく考慮して実装したいですね。
以上です。
Discussion