optional where in stored proc

david_ste · August 5th, 2005, 05:38 AM

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.

joefawcett · August 6th, 2005, 05:20 AM

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)

ugo · October 27th, 2005, 07:16 AM

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...

--
 Ugo