Stored Procedure with optional "WHERE" parameters
mysql, oracle, sql, sql-server
Solution
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.
Problem
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.