pistacchio pistacchio - 2 days ago 6
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

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.

Comments