case when used in Where clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

case when used in Where clause

Author

Topic

allan8964
Posting Yak Master

249 Posts

Posted - 2012-02-19 : 12:53:55

Hi there,

I tried to use case when statement in where clause in a stored procedure:

Select ... from ...
Where code = 'abc' and name = 'John'
AND condition3

Here I have 3 different condition3 as follows:
cond1: (UserCode = 'Started' or UserCode = 'Done' or UserScore > 100)
cond2: (UserCode = '' and SysCode = 'FGP005')
cond3: (GroupCode = '' or SysCode = 'PBD302')

There is a parameter @SiteName which has three values as A, B, C. When @SiteName='A' then cond1 is used, B is used for cond2 and C for con3:

if @SiteName='A' then the where clause should be:
Where code = 'abc' and name = 'John'
AND (UserCode = 'Started' or UserCode = 'Done' or UserScore > 100)

if @SiteName='B' then
Where code = 'abc' and name = 'John'
AND (UserCode = 'Done' and SysCode = 'FGP005')

...

So how can I use the case when in it? Thanks in advance.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-19 : 13:32:42

You probably can, but you're likely to have this kind of problem: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

--
Gail Shaw
SQL Server MVP

allan8964
Posting Yak Master

249 Posts

Posted - 2012-02-21 : 12:49:28

Thanks. But I don't think this is the same case. I did some research and I found that it's impossible to add equations as "UserCode='Done'" after then. Only a string, a col name or a value can be used after then ... If I am wrong correct me, plz. So for my scenario I think I need to create more sp to call these three different where clauses.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-21 : 14:55:43

It's not the same case, but it is similar. You can do all of that in one query and with one where clause, but you'll run into the kind of performance problems that the blog post talks about.

I do recommend multiple stored procedures. Easier to write and most likely to perform better than any all-in-one solution.

--
Gail Shaw
SQL Server MVP

allan8964
Posting Yak Master

249 Posts

Posted - 2012-02-21 : 15:54:06

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources