If I understand you correctly, this should work (not to mention get rid
of your dynamic sql):

CREATE PROC get_recordset
@color1 varchar(100) = NULL,
@color2 varchar(100) = NULL,
@format1 varchar(100) = NULL,
@format2 varchar(100) = NULL
AS
SELECT *
FROM table
WHERE (color IN (@color1, @color2) OR (@color1 IS NULL AND @color2 IS
NULL))
AND (format IN (@format1, @format2) OR (@format1 IS NULL AND @format2 IS
NULL))
go

If you don't pass in any colors, then the first part of the where clause
will be true, and it will only try to filter on the formats.

If you don't pass in any formats, then the second part of the where
clause will be true, and it will only try to filter on the colors.

If you pass both color(s) and format(s), it will try to filter on both.

-----Original Message-----
From: Justin Gilli [mailto:[Email Address Removed]
Sent: Friday, April 30, 2004 11:04 AM
To: LazyDBA.com Discussion
Subject: And/Or Problem



I have an asp app passing checkbox parameters to another page via
request.querystring to a SQL statement.

I build a recordset based on these variables.

The fields: Format and Color.

Example:

Sql = "select * from table where (Color = '" & Query1 & '" or '" &
Query2 & '") or (Format = '" & Query3 & '" or '" & Query4 & '")"

The problem I have is that if the user submits variables from different
categories such as Format and Color the string will return all items
that match these criteria rather then the specific results. This is
because I have "or" in this case. Changing to "and" gives me too few
results. I need to dynamically determine the and/or based on the
categories submitted.

I assume I will need to handle this somehow in a Stored Procedure.
Can anyone steer me in the right direction on this one?



Sincerely,

Justin






MS Sql Server LazyDBA home page