Web application user queries - 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)

Web application user queries

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2005-09-23 : 05:39:37

I have a web form page to search products sells, in that form the user can specify a family a product and a city. Now I am designing the SP to return the results of the user query, but I don’ t know how to handle the ‘Where’ clause since the user can specify only a family, a family and a city, a family and a product , a family a product and a city and only a city.

So, my question is: How ‘Where’ clause must be to combine all these possibilities?

The logic would be something like this (but this isn’ t correct) :


Select Data, Oferta_id, title, company, city_name
From Offers

If @Family <> 0 And @Product = 0 And @City <> 0
 Begin
  Where family = @Family And city = @City 
 End     
If @Family <> 0 And @Product <> 0 And @City <> 0
 Begin
  Where family = @Family And product = @Product And city = @City 
 End     
...

Thank you

fantasma
Starting Member

8 Posts

Posted - 2005-09-23 : 06:02:22

Have you tried putting the SELECT ... FROM Offers inside each conditional statement? I'm not sure if it works this way but perhaps it's interpreting it as a many different statements as opposed to a single concatenated one?

Even if it was concatenating it you'd have something like...

Select Data, Oferta_id, title, company, city_name
From Offers
Where family = @Family And city = @City
Where family = @Family And product = @Product And city = @City

...if all of the conditions were true. I don't think you're allowed more than a single WHERE (operator/condition/appropriate term).

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-23 : 06:02:25

select Data, Oferta_id, title, company, city_name
from offers
where (family = @family and @product = 0 and @city = 0)
or (family = @family and product = @product and @city = 0)
or (family = @family and product = @product and city = @city)
or (@family = 0 and @product = 0 and city = @city)
or (@family = 0 and product = @product and city = @city)
or (@family = 0 and product = @product and @city = 0)
or (family = @family and @product = 0 and city = @city)

Duane.

cesark
Posting Yak Master

215 Posts

Posted - 2005-09-23 : 06:11:30

Hi Duane

And depending on what this 'Select' will choose one condition or another? I mean, it will not choose always the first one? 'where (family = @family and @product = 0 and @city = 0)'

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-23 : 06:16:30

I'm assuming if the user does not want to filter on product and city then these variables ie @product and @city will be passed a value of 0 by the proc. then the first line of the where clause will be applied the other conditions are covered by the other lines. Have you tried it out to see if it works?
from my understanding of the question it seemed to work when I tested it

Duane.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-09-23 : 06:24:43

here is how i tested it

create table #test
(
Family INT,
Product INT,
City INT
)

INSERT INTO #test values(1, 1, 1)
INSERT INTO #test values(1, 1, 2)
INSERT INTO #test values(1, 1, 3)
INSERT INTO #test values(1, 2, 1)
INSERT INTO #test values(1, 2, 2)
INSERT INTO #test values(1, 2, 3)
INSERT INTO #test values(1, 3, 1)
INSERT INTO #test values(1, 3, 2)
INSERT INTO #test values(1, 3, 3)
INSERT INTO #test values(2, 1, 1)
INSERT INTO #test values(2, 1, 2)
INSERT INTO #test values(2, 1, 3)
INSERT INTO #test values(2, 2, 1)
INSERT INTO #test values(2, 2, 2)
INSERT INTO #test values(2, 2, 3)
INSERT INTO #test values(2, 3, 2)
INSERT INTO #test values(2, 3, 3)
INSERT INTO #test values(3, 1, 1)
INSERT INTO #test values(3, 1, 2)
INSERT INTO #test values(3, 1, 3)
INSERT INTO #test values(3, 2, 1)
INSERT INTO #test values(3, 2, 2)
INSERT INTO #test values(3, 2, 3)
INSERT INTO #test values(3, 3, 1)
INSERT INTO #test values(3, 3, 2)
INSERT INTO #test values(3, 3, 3)

create proc testprc
(
@family int,
@product int,
@city int
)
as

select * from #test
where (family = @family and @product = 0 and @city = 0)
or (family = @family and product = @product and @city = 0)
or (family = @family and product = @product and city = @city)
or (@family = 0 and @product = 0 and city = @city)
or (@family = 0 and product = @product and city = @city)
or (@family = 0 and product = @product and @city = 0)
or (family = @family and @product = 0 and city = @city)

exec testprc 1, 2, 3
select * from #test where family = 1 and product = 2 and city = 3

exec testprc 0, 0, 1
select * from #test where city = 1

exec testprc 1, 0, 3
select * from #test where family = 1 and city = 3

exec testprc 0, 2, 0
select * from #test where product = 2

Duane.

cesark
Posting Yak Master

215 Posts

Posted - 2005-09-23 : 07:07:41

Oh yes, it is true, it works fine!

Thank you very much. I' ve never seen this before (I made very few filtered queries before), and the new thing to me is how to tell 'Select' statement avoid searching some fields the user don' t want (for example @Product = 0 in my case).

Regards,
Cesar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources