Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server ASP
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
Old August 5th, 2005, 05:38 AM
Registered User
Join Date: Aug 2005
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.

Old August 6th, 2005, 05:20 AM
joefawcett's Avatar
Wrox Author
Join Date: Jun 2003
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts

If your business area select is named "lstBusinessArea" and looks something like:
<select name="lstBusinessArea">
  <option value="">(All)</option>
  <option value="1">Book Sales</option>
  <option value="2">Record Sales</option>
The your code will be like:
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)
Old October 27th, 2005, 07:16 AM
ugo ugo is offline
Registered User
Join Date: Oct 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to ugo

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

  @param1 int = NULL,
  @param2 int = NULL

 FROM [mytable]
          WHEN @param1 IS NULL THEN 1
              WHEN param1 = @param1 THEN 1
              ELSE 0
        END = 1) AND
          WHEN @param2 IS NULL THEN 1
              WHEN param2 = @param2 THEN 1
              ELSE 0
        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...


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

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