Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
Register | FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #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.

Reply With Quote
  #2 (permalink)  
Old July 8th, 2008, 03:30 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
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.
Reply With Quote
  #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
")

Reply With Quote
  #4 (permalink)  
Old July 9th, 2008, 02:37 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,652
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
Reply With Quote
  #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!

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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



All times are GMT -4. The time now is 12:18 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.