dynamic where clause - 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 2000 Forums

SQL Server Development (2000)

dynamic where clause

Author

Topic

Zim327
Yak Posting Veteran

62 Posts

Posted - 2009-10-28 : 15:57:15

I created a dynamic sql query from a tutorial which said always write the where clause like this " where 1 = 1 "...
so the where clause will always work.
However, this does work as long as you AND any additional search params.
Once I modified the query to use OR as well. I always received too much information
such as this


select c.ConID, c.ContractNum, c.PoPStart, c.PoPEnd, p.ProgramName, j.ProjName 
from 
Contracts c inner join Programs p on c.ProgramID = p.ProgramID 
inner join Project j on j.ProjID = c.ProjID 
where 1 = 1 or c.ConID > 50 or c.PoPStart > '2009-10-01' order by c.ConID asc

results in too much information returned.
Is there any easy way to fix this?
Thanks,

Zim327
Yak Posting Veteran

62 Posts

Posted - 2009-10-28 : 16:14:15

Ok I figured it out!
Luckily this was an easy fix:
right after the where clause:
if the user chooses all fields (i.e. AND)
then use 1 = 1
if the user chooses any field (i.e. OR)
then use 1 = 0

so in the OR case:


select c.ConID, c.ContractNum, c.PoPStart, c.PoPEnd, p.ProgramName, j.ProjName 
from 
Contracts c inner join Programs p on c.ProgramID = p.ProgramID 
inner join Project j on j.ProjID = c.ProjID 
where 1 = 0 or c.ConID > 50 or c.PoPStart > '2009-10-01' order by c.ConID asc

I tested it out and it works perfectly.
Best regards,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources