Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old July 8th, 2008, 01:56 PM
Authorized User
 
Join Date: Jul 2006
Location: , , .
Posts: 22
Thanks: 1
Thanked 0 Times in 0 Posts
Default Selecting from multiple drop-lists

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.

  #2 (permalink)  
Old July 8th, 2008, 03:30 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
  #3 (permalink)  
Old July 9th, 2008, 02:33 PM
Authorized User
 
Join Date: Jul 2006
Location: , , .
Posts: 22
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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
")

  #4 (permalink)  
Old July 9th, 2008, 02:37 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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
  #5 (permalink)  
Old July 9th, 2008, 03:34 PM
Authorized User
 
Join Date: Jul 2006
Location: , , .
Posts: 22
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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



Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with dynamic drop down lists MarkGT Classic ASP Basics 3 April 29th, 2008 06:05 PM
Linked Drop Down Lists contagiouss_blue Excel VBA 6 June 8th, 2005 09:02 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.