Conditional WHERE caluse - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Conditional WHERE caluse

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-06 : 12:02:15

Greetings,

Hope you had a nice weekend! Where can I go for conditional where clause, can you point me to a good article.
Basically what I am trying to is as follows

I have a bit parameter "WeeklyYesNo". If true where clause will be

WHERE DefinitionDate >= @Date
AND DefinitionDate <= dbo.dt_EndOfWeek(@Date,7)

Otherwise it will be
WHERE DefinitionDate = @Date
Can I do that in one shot or make it IF ELSE thingy?

Muchisimas Gracias!

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-04-06 : 13:52:41

This might be enough for you


WHERE
  (  
        @Weekly         = 1
    AND DefinitionDate >= @Date
    AND DefinitionDate <= dbo.dt_EndOfWeek(@Date,7)
     
   )
OR
   (    
         @Weekly        = 0
     AND DefinitionDate = @Date
   )

Jim

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-04-06 : 14:03:14

As tempting as it is to have a conditional where clause all lumped into 1 query, check the execution of the queries and see if the added time justifies it. I would probably dynamically create the where clause, or just have 2 seperate queries. The performance should increase that way due to the simpler execution plan.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-06 : 14:24:05

Thanks Jim et Vinnie! Let me try that and post the result. Actually it is a simple query that involves one table but I will do as suggested.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2009-04-07 : 01:50:56

Hi,
try this.

where (@weekly=0 or (DefinitionDate >= @Date and DefinitionDate <= dbo.dt_EndOfWeek(@Date,7)))
and (@weekly=1 or DefinitionDate = @Date)

performance is also far better than case clause
Kunal

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-07 : 02:39:21

Refer
http://www.sommarskog.se/dyn-search-2005.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources