pistacchio pistacchio - 1 year ago 62
MySQL Question

Stored Procedure with optional "WHERE" parameters

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.

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download