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.

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.