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.

Solution 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.

Solution 2

Create your procedure like this:

CREATE PROCEDURE [dbo].[spXXX]
    @fromDate datetime = null,
    @toDate datetime = null,
    @subCode int = null
as
begin
set NOCOUNT ON
/* NOCOUNT limits the server feedback on select results record count */
SELECT
    fields...
FROM
    source
WHERE
    1=1
--Dynamic where clause for various parameters which may or may not be passed in.
and ( @fromDate is null or [dateField] >= @fromDate)
and ( @toDate is null or [dateField] <= @toDate)
and ( @subCode is null or subCode= @leaveTypeSubCode)
order by fields...

This will allow you to execute the procedure with 0 params, all params, or any # of params.

Solution 3

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)

Solution 4

A readable and maintainable way to do it (even usable with JOIN/APPLY) :

where 
      (@parameter1 IS NULL OR your_condition1)
  and (@parameter2 IS NULL OR your_condition2) 
-- etc

However it's a bad idea on most big tables (even more using JOIN/APPLY), since your execution plan will not ignore NULL values and generates massive performance loophole (ex : scaning all a table searching for NULL values).

A roundabout way in SQL Server is to use WITH(RECOMPILE) options in your query (available since SQL 2008 SP1 CU5 (10.0.2746)).

The best way to implements this (performance wise) is to use IF ... ELSE block, one for each combination possible. Maybe it's exhausting but you will have the best performances and it doesn't matter your database settings.

If you need more details, you can look for KM. answer here.

Solution 5

You can do something like

WHERE 
(
 ParameterA == 4 OR ParameterA IS NULL
)

AND
(
 ParameterB == 12 OR ParameterB IS NULL
)

View more solutions

Comments

[Eppz]

This doesn't allow for all parameters to be optional. In this example @status_id must be passed in. Even if it's null, you have to pass null in for this to execute.

[Tom H]

You can provide a default value for parameters in MS SQL Server. I don't know about MySQL

[Tom H]

Just keep in mind that depending on your RDBMS and how it caches query plans, you might not get the best possible performance with this method.

[NotMe]

@Tom: agreed. In this case, performance takes back seat to security and flexibility. The hard part is the number of variables the OP talked about. It makes building the right indexing near impossible; so the best bet is to watch usage and build the indexes on the most common search pattern.

[E_the_Shy]

Another danger with this is that you somehow manage to pass in NULL for everything and then every row comes back. I saw this happen in an application and it tried to send 1 million+ rows back to the client.

[NotMe]

@ww: yeah, you should do some simple checking on the front end to decide if you want to allow a full listing or not.

[StuartLC]

More on the potential performance issue in SQL Server relating to this pattern here when used in a Stored Procedure - parameter sniffing can be a problem if the table(s) involved have a large number of rows

[NotMe]

Just wanted to add a note about our usage of this pattern. One of our tables has about a million records, the search query has 10 optional parameters. The query results are near instant.

[DeveloperDan]

DO NOT USE IN T-SQL. If a field is nullable the condition NULL = NULL will NOT return TRUE. see: sommarskog.se/dyn-search-2008.html (look for the Coalesce Trap)