Stored Procedure with optional "WHERE" parameters

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.