Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server ASP
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Server ASP Discussions about ASP programming with Microsoft's SQL Server. For more ASP forums, see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server ASP 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
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 5th, 2005, 05:38 AM
Registered User
 
Join Date: Aug 2005
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default optional where in stored proc

I have a web page I want to run a stored procedure from. In the web page I have three drop downs

Business Area, Business Unit and Reporting Period

The drop downs determine whether all projects are returned or all projects for a certain business unit / business area or month.

This means I have to tailor my sql statement accordingly. What I want to know is can I append sections of a sql statement ie add or subtract more where clauses depending on the values pulled in from the web page.

At the moment I have only accounted for 2 variables and that has caused me to create 4 IF statements depending on the values. 3 variables would cause even more IF statements and multiple combinations which I am trying to avoid.

Reply With Quote
  #2 (permalink)  
Old August 6th, 2005, 05:20 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

If your business area select is named "lstBusinessArea" and looks something like:
Code:
<select name="lstBusinessArea">
  <option value="">(All)</option>
  <option value="1">Book Sales</option>
  <option value="2">Record Sales</option>
</select>
The your code will be like:
Code:
Dim iBusinessArea
iBusinessArea = Request("lstBusinessArea")
Dim sSQL
sSQL = "SELECT * FROM tblSales WHERE(BusinessAreaId = "
sSQL = sSQL & iBusinessArea
sSQL = sSQL & " OR '" & iBusinessArea & "' = '')"
Normally I'd try to use a stored procedure for both performance, security and maintainability but the principle is the same.

--

Joe (Microsoft MVP - XML)
Reply With Quote
  #3 (permalink)  
Old October 27th, 2005, 07:16 AM
ugo ugo is offline
Registered User
 
Join Date: Oct 2005
Location: Bogot√°, , Colombia.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to ugo
Default

I've been using this code recently, and works very good:

CREATE PROCEDURE foo
  @param1 int = NULL,
  @param2 int = NULL
AS

SELECT *
 FROM [mytable]
 WHERE (CASE
          WHEN @param1 IS NULL THEN 1
          ELSE
            CASE
              WHEN param1 = @param1 THEN 1
              ELSE 0
            END
        END = 1) AND
       (CASE
          WHEN @param2 IS NULL THEN 1
          ELSE
            CASE
              WHEN param2 = @param2 THEN 1
              ELSE 0
            END
        END = 1)

So if you don't pass @param1 and @param2, the SP would be the same that a simple 'SELECT * FROM [mytable]', but if you pass some of the parameters, the query will work according to that.

Hope it helps...



--
&nbsp;Ugo
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
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
Stored Proc that returns a value elygp SQL Server 2000 4 May 9th, 2007 01:05 AM
Calling an insert stored proc from a select stored dzitam SQL Language 10 April 2nd, 2007 12:39 PM
How to get value from stored proc busybee ASP.NET 1.0 and 1.1 Basics 4 April 2nd, 2006 01:06 AM
Optional Criteria in Stored Procedures shaileshmark SQL Server 2000 24 July 17th, 2004 09:06 PM
Optional Stored Proc Parameters? VBAHole22 SQL Server 2000 3 August 13th, 2003 11:46 AM



All times are GMT -4. The time now is 06:00 AM.


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