Stored Procedure with optional "WHERE" parameters

Question

Hi to all. I have a form where users can specify various parameters to dig some data (status, date ecc).

I can producea query that is: SELECT * FROM table WHERE: status_id = 3 date = other_parameter =

etc. Each WHERE is optional (i can select all the rows with status =3, or all the rows with date = 10/10/1980, or all the rows with status = 3 AND date = 10/10/1980 ecc.

Given a large number of parameters, all optional, what is the best way to make up a dynamic stored procedure?

I'm working on various DB: MySQL, Oracle and SQLServer.

Answer 1

One of the easiest ways to accomplish this:

SELECT * FROM table 
WHERE ((@status_id is null) or (status_id = @status_id))
and ((@date is null) or ([date] = @date))
and ((@other_parameter is null) or (other_parameter = @other_parameter))

etc. This completely eliminates dynamic sql and allows you to search on one or more fields. By eliminating dynamic sql you remove yet another security concern regarding sql injection.

Answer 2

You can do something like

WHERE 
(
 ParameterA == 4 OR ParameterA IS NULL
)

AND
(
 ParameterB == 12 OR ParameterB IS NULL
)

Answer 3

Create your procedure like this:

CREATE PROCEDURE [dbo].[spXXX]
    @fromDate datetime = null,
    @toDate datetime = null,
    @subCode int = null
as
begin
set NOCOUNT ON
/* NOCOUNT limits the server feedback on select results record count */
SELECT
    fields...
FROM
    source
WHERE
    1=1
--Dynamic where clause for various parameters which may or may not be passed in.
and ( @fromDate is null or [dateField] >= @fromDate)
and ( @toDate is null or [dateField] <= @toDate)
and ( @subCode is null or subCode= @leaveTypeSubCode)
order by fields...

This will allow you to execute the procedure with 0 params, all params, or any # of params.

Answer 4

If you want to avoid dynamically building up SQL strings (which is often best avoided), you can do this in stored procs by comparing each critera in your where claused with a default value, which equates to "ignore". E.g.:

select * from Table where
   (@Col1 IS NULL OR Col1 = @Col1) /*If you don't want to filter in @col, pass in NULL*/
   AND
   (@Col2 IS NULL OR Col2 = @Col2)