Alex Rivera | Logout

Stored Procedure with optional "WHERE" parameters

Asked 2009-03-30T15:21:42.347
49

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.

sql mysql sql-server oracle-database
Edit
Report

2 Answers

69

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.

answered 2009-03-30T15:25:29.343
5

This is the style I use:

t-sql 

SELECT    *        
FROM    table        
WHERE     
status_id    =    isnull(@status_id ,status_id)     
and    date    =    isnull(@date ,date )     
and    other_parameter    =    isnull(@other_parameter,other_parameter)

oracle 

SELECT    *        
FROM    table        
WHERE     
status_id    =    nval(p_status_id ,status_id)     
and    date    =    nval(p_date ,date )     
and    other_parameter    =    nval(p_other_parameter,other_parameter)
answered 2012-11-08T10:11:43.770

Your Answer