Selecting from multiple drop-lists

Earl Hickey · July 8th, 2008, 01:56 PM

Say for example I have 3 drop-lists (categories) in a web application that users can select from. I want to build a simple select from my table using each list as the criteria. When nothing is selected from a particular list, all items in that category should be returned (provided they meet the criteria elsewhere). What's the easiest way to build my query? It seems like "if @dd1 != 'NONE' and @dd2 != 'NONE' and @dd3 != 'NONE' select", etc. is quite a bit of a pain.

Old Pedant · July 8th, 2008, 03:30 PM

Well, if you use an ad hoc query, instead of a stored proc, it's easy enough:

Use value="" for the defaults:

<SELECT name="dd1">
    <OPTION value=0>Any</OPTION>
    <OPTION value=1>First</OPTION>
    <OPTION value=2>Second</OPTION>
    ...

And then build up the SQL (example in VBScript code, but any language similar):
    <%
    SQL = "SELECT * FROM table WHERE 1=1 "
    dd = CLNG(Request("dd1"))
    If dd > 0 Then SQL = SQL & " AND dd1=" & dd
    dd = CLNG(Request("dd2"))
    If dd > 0 Then SQL = SQL & " AND dd2=" & dd
    dd = CLNG(Request("dd3"))
    If dd > 0 Then SQL = SQL & " AND dd3=" & dd

But if you want to use a stored proc you need to get trickier.

Code:

CREATE PROC demo
    @dd1 INT, @dd2 INT, @dd3 INT
AS
SELECT * FROM table
WHERE ( CASE WHEN @dd1 = 0 THEN dd1 ELSE @dd1 ) = dd1
  AND ( CASE WHEN @dd2 = 0 THEN dd2 ELSE @dd2 ) = dd2
  AND ( CASE WHEN @dd3 = 0 THEN dd3 ELSE @dd3 ) = dd3

Lots of other ways to do this, I'm sure, but that should work.

Earl Hickey · July 9th, 2008, 02:33 PM

when I try that, I get the error: "Error 170: Line 60: Incorrect syntax near ')'." (line 60 is "WHERE ( CASE WHEN @dd1 = 0 THEN dd1 ELSE @dd1 ) = dd1
")

Old Pedant · July 9th, 2008, 02:37 PM

I just left out the END keyword.

I dunno why, but I do that all the time.

Code:

CREATE PROC demo
    @dd1 INT, @dd2 INT, @dd3 INT
AS
SELECT * FROM table
WHERE ( CASE WHEN @dd1 = 0 THEN dd1 ELSE @dd1 END ) = dd1
  AND ( CASE WHEN @dd2 = 0 THEN dd2 ELSE @dd2 END ) = dd2
  AND ( CASE WHEN @dd3 = 0 THEN dd3 ELSE @dd3 END ) = dd3

Earl Hickey · July 9th, 2008, 03:34 PM

Ah, I should have caught the missing "end". I think I'm on the right track now, thanks!