sql - Stored Procedure with optional "WHERE" parameters

Question

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

I can produce a query that is:

SELECT * FROM table WHERE:
status_id = 3
date = <some date>
other_parameter = <value>

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

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, such as: MySQL, Oracle and SQLServer.

question from:https://stackoverflow.com/questions/697671/stored-procedure-with-optional-where-parameters

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.