- PR -

SQLServer2005のストアド内でのWHERE句について

1
投稿者投稿内容
kureru
常連さん
会議室デビュー日: 2005/07/26
投稿数: 22
投稿日時: 2007-02-14 11:47
お世話になります。

現在SQLServer2005でのストアドプロシージャを利用しています。
パラメータが渡される場合と、空文字で渡さる場合があり、
パラメータが渡された場合のみ、where句に反映したいのですが、
どのように記述すればよいのでしょうか?

現状、3つの方法を思いついたのですが、

1.
select
...
where (CODE >= @CODE OR @CODE ='')

2.
select
...
where
case when @CODE = '' then 1
else
case when CODE >= @CODE then 1
else 0
end
end = 1

3.
declare @WHERE nvarchar(100)
if @CODE <> ''
set @WHERE = 'where CODE >= ' + @CODE
else
set @WHERE = ''

exec('select .... ' + @WHERE)


3の場合、SQL文自体の記述が書きにくいため、極力避けたいのですが、
3のほうが実行速度は速いのでしょうか?
1や2だと、実行速度にかなりの差がでてしまうのでしょうか?
もしくは、別にオーソドックスな方法があるのでしょうか?

ご存知の方がいれば教えていただけませんでしょうか。
よろしくお願いします。
かずくん
ぬし
会議室デビュー日: 2003/01/08
投稿数: 759
お住まい・勤務地: 太陽系第三惑星
投稿日時: 2007-02-14 12:58
nullが渡されるのなら、
コード:
select
    ...
where 
    case when not (
        CODE >= @CODE
    ) then 1 else 0 end = 0


ていうのが好きだけど、

空文字列なら、1の方法かねぇ
ue
ぬし
会議室デビュー日: 2005/05/07
投稿数: 581
お住まい・勤務地: 広島市
投稿日時: 2007-02-14 13:46
こんにちは。

読み易い気がするこれを使っています。
コード:

WHERE 列名 =
CASE WHEN (@parameter IS NULL) OR (@parameter = '')
THEN 列名
ELSE @parameter
END


せっかくの Index Seek が Clusterd Index Scan になるので遅いですが。

_________________
上本亮介 (ue) @ わんくま同盟
Microsoft MVP for Microsoft Office System - Microsoft Office System
(Jul 2006 - Jun 2007)
Hello Another World!

[ メッセージ編集済み 編集者: ue 編集日時 2007-02-14 13:46 ]
kureru
常連さん
会議室デビュー日: 2005/07/26
投稿数: 22
投稿日時: 2007-02-14 14:20
かずくんさん、ueさんご回答ありがとうございます。

引用:

WHERE 列名 =

CASE WHEN (@parameter IS NULL) OR (@parameter = '')

THEN 列名

ELSE @parameter

END



こんな方法もあるんですね。
読みやすいので、これでいこうと思います。
未記入
ぬし
会議室デビュー日: 2004/09/17
投稿数: 667
投稿日時: 2007-02-14 14:55
引用:
こんな方法もあるんですね。
読みやすいので、これでいこうと思います。


私には読みやすいとは到底思えません。これはとてもトリッキーな書き方ですよ。
「例外条件の場合に列の値を返すことで、常に等式が成り立つように細工する」という方法です。

どうしてもスカラ値を返さないといけない文脈上の制限がある場合に
このような記述を使うことはありますが、今回のケースで使う意味はないでしょう。
可読性が低下するだけです。

こんな書き方をするよりも、素直に 1. の方法で
where (CODE >= @CODE OR @CODE ='') と書いたほうが良いです。
これは「条件を満たすか、または 例外条件である」と素直に読めます。

パフォーマンス上の問題がある場合は、方法 3. も検討してください。
方法 3. には @CODE の値に応じて実行プランが決定されるというメリットがあります。
ue
ぬし
会議室デビュー日: 2005/05/07
投稿数: 581
お住まい・勤務地: 広島市
投稿日時: 2007-02-14 16:21
引用:

未記入さんの書き込み (2007-02-14 14:55) より:

どうしてもスカラ値を返さないといけない文脈上の制限がある場合に
このような記述を使うことはありますが、今回のケースで使う意味はないでしょう。
可読性が低下するだけです。


仰る通りです。
私が示したのはパラメータの対象となる列が実行時に決まるようなクエリに使用している方法で、kureruさんの示された条件には合わないですね。

みなさま、失礼しました。
_________________
上本亮介 (ue) @ わんくま同盟
Microsoft MVP for VSTO (Jul 2008 - Jun 2009)
Hello Another World!
.NET 勉強会 / ヒーロー島
未記入
会議室デビュー日: 2006/11/10
投稿数: 12
投稿日時: 2007-02-16 11:26
条件があいまいなんです。
というか、隠れた条件があるのか。

>パラメータが渡された場合のみ、where句に反映したいのですが、
 ↓
パラメータが渡された場合、where句に反映したい。
パラメータが渡されなかった場合(空文字)、全件ヒットしたい。

そうすればおのずと何書けばいいかわかるでしょ。
素直に書きましょ。

こばさん
大ベテラン
会議室デビュー日: 2004/03/17
投稿数: 147
投稿日時: 2007-02-16 15:40
自分ならば

select
...
where CODE>=ISNULL(@CODE,CODE)

かな
1

スキルアップ/キャリアアップ(JOB@IT)