Checkboxes and SQL Queries

[hc1619]

Hi there. Me again.. on my site (dating site) I have an advanced search feature which lets people get specific about their criteria. One thing I don't have I'd LIKE to have is checkboxes instead of drop downs.

Lets say I'm giving the user the option of choosing the hair colour.. at the moment I have a drop down box listing out the 8 different hair colour types.. they only have 1 choice.. then I carry that value into my stored proc and return all matches with only that hair colour.

What i'd really like to do is have checkboxes allowing people to tick multiple hair colours for their results. THe only problem is I have no idea how to pass this info and write it into my SQL query in the stored proc?

Can anyone give me some suggestions on how this is done?

 

[amorous]

I guess something like this...

If request("checkboxname")="ON" <-- means that it has been checked
Then
You can pass this variable like any other variable into your stored proc

-VJ

 

[checkai]

you'd have to have the stored proc accept a value for each available checkbox.

 

[hc1619]

maybe i wasnt very clear in my question.

i know how to get the value of a checked/unchecked box.. what i need to know is the best way to structure my WHERE query in the SQL. I mean lets say I pass in 8 variables in my stored proc... I can't think of a logical dynamic way to construct the WHERE query in my SQL based on all these variables coming in.

In coldfusion I could do this:

SELECT *
FROM BLAH
WHere COuntry = 1
<cfif IsDefined(FOrm.HairRED) Is True>and Hair = 1</cfif>
<cfif IsDefined(FOrm.HairBLUE) Is True>and Hair = 2</cfif>
<cfif IsDefined(FOrm.HairBLUE) Is True>and Hair = 3</cfif>
and so on.......

But I dont know how to construct similar conditional where clauses inside of a stored procedure.

 

[NoCoolHandle]

Somehting like this?

Create Proc ABC
@x int,
@y int,
@z int
as
if @x between 2 and 20 
   Begin
     print 'between 2 and 20'
     select @y = @x * @z 'Change the value of y
     select @y ' Return the value to a recordset (cursor)
   end
else if @y = 30
    Begin
      select '@x wasn''t between 2 and 20  but @y was 30'
    End

Rob

 

[SQLSister]

Have you thought about using the in clause?

Questions about posting. See faq183-874