Stored Procedure with optional “WHERE” parameters

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:

  • SQL Query Table for Changes to record based on previous record (SQL Server)
  • SQL Row Concatenation on joined table
  • SQL SERVER 2014 - SELECT permission was denied on View
  • SQL: Matching parents and children from table
  • SQL Server simple Insert statement times out
  • Can you use JDBC to connect to just an instance without specifying a database?
  • 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.

  • Impacts on deleting FKs
  • SQL Server : dynamic pivot over 5 columns
  • Optimize inserts
  • Array like functionality in SQL Server 2008
  • MSSQL: update matching rows from the uploaded file
  • TSQL make trigger fail silently
  • 6 Solutions collect form web for “Stored Procedure with optional “WHERE” parameters”

    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.

    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.

    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)
    

    You can do something like

    WHERE 
    (
     ParameterA == 4 OR ParameterA IS NULL
    )
    
    AND
    (
     ParameterB == 12 OR ParameterB IS NULL
    )
    

    If you want to avoid dynamically building up SQL strings (which is often best avoided), you can do this in stored procs by comparing each critera in your where claused with a default value, which equates to “ignore”. E.g.:

    select * from Table where
       (@Col1 IS NULL OR Col1 = @Col1) /*If you don't want to filter in @col, pass in NULL*/
       AND
       (@Col2 IS NULL OR Col2 = @Col2)
    

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.