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

rwlopez
Yak Posting Veteran

80 Posts

Posted - 2006-12-18 : 14:48:18

I have a scenerio where I need to alter a section of a WHERE Clause depending on the input I receive from a user, but I am having trouble. This is what I am trying to do can anyone suggest an alternative way of accomplishing this. The syntax below will not work I am only putting it an example of what I need to do. The bottom line is that I need the criteria of the query to change depending on user input.

DECLARE @Value AS int

SET @Value = 'I'

SELECT *
FROM SalesPeople
WHERE CASE
WHEN @Value = 'I'
THEN SalesOpenDate > ‘1/1/2006’
WHEN @Value = 'N'
THEN SalesCloseDate < ‘1/1/2006’

X002548
Not Just a Number

15586 Posts

Posted - 2006-12-18 : 15:14:31

IF @Value = 'I
SELECT *
FROM SalesPeople
WHERE SalesOpenDate > ‘1/1/2006’

IF @Value = 'N'
SELECT *
FROM SalesPeople
WHERE SalesCloseDate < ‘1/1/2006’

????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-12-18 : 16:07:23

Also,

There are several articles posted here about "dynamic Where" clauses. Try a site search for more ideas.

one possibility:

select * from salespeople
where salesclosedate > case when @value = 'i' then '1/1/2006' else '1/1/1900' end
and salesclosedate < case when @value = 'n' then '1/1/2006' else '1/1/3000' end

Be One with the Optimizer
TG

rwlopez
Yak Posting Veteran

80 Posts

Posted - 2006-12-18 : 16:18:51

I was going to take the approach with the two SELECT statements, but I am actually using SELECT statement that is fairly long and I didn't want to have duplicate or nearly duplicate code. I also tried to accomplish this by using the second approach mentioned, but it was a little problematic, although I think that I can make it work. I used to do stuff like this in VB all the time with SQL strings, but those SQL statement were just strings. I would like to something like this, but I am not sure if there is a way to make it work. Anyone have any ideas?

DECLARE @Value AS int
DECLARE @Text AS varchar(100)

SET @Value = 'I'

CASE
WHEN Value = 'I'
THEN @Text = 'SalesOpenDate > ‘1/1/2006’'
WHEN Value = 'N'
THEN @Text = 'SalesCloseDate < ‘1/1/2006’

SELECT *
FROM SalesPerson
WHERE @Text

rwlopez
Yak Posting Veteran

80 Posts

Posted - 2006-12-18 : 16:31:09

Actually I think I found a post that will help me do what I want to do. Thanks for all of your help.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-18 : 17:08:18

Erland has a good article about this as well:

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

www.elsasoft.org

X002548
Not Just a Number

15586 Posts

Posted - 2006-12-19 : 09:26:04

Yes but performance wise you'd be better off with the 2 queries. You're just gonna cut and paste them, so what's the big deal?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-19 : 10:05:13

Care to share the link you've found?

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources