How to Handle Optional Parameters in SQL Query

How to handle optional parameters in SQL query?

Yes, using any of the following:

WHERE m.id_pk = NVL(n_RequiredId, m.id_pk);
WHERE m.id_pk = COALESCE(n_RequiredId, m.id_pk);
WHERE (n_RequiredId IS NULL OR m.id_pk = n_RequiredId);

...are not sargable. They will work, but perform the worst of the available options.

If you only have one parameter, the IF/ELSE and separate, tailored statements are a better alternative.

The next option after that is dynamic SQL. But coding dynamic SQL is useless if you carry over the non-sargable predicates in the first example. Dynamic SQL allows you to tailor the query while accommodating numerous paths. But it also risks SQL injection, so it should be performed behind parameterized queries (preferably within stored procedures/functions in packages.

How to handle optional parameter used in SQL query IN clause

Building your query dynamically is the way to go, I would expect. Criteria can work if you have a base object, and hibernate filters are amazing. A simple query generation could look something like this:

private final String the_Query = "SELECT a.item_type, a.item_flag, a.item_id FROM tableA a";

private String addWhereClause(String whereClause, boolean whereClauseAdded){
    String returnValue = "";

    if (!whereClauseAdded){
        returnValue = " where " + whereClause;
    }else{
        returnValue = whereClause;
    }

    return returnValue;
}

private StringBuilder generateQuery(String itemType, int itemFlag, List<Long> itemIds){
    StringBuilder b = new StringBuilder();
    b.append(the_Query);

    boolean whereClauseAdded = false;
    String whereClause = "";

    if (itemType != null){
        whereClause = " a.item_type = " + itemType;
        b.append(addWhereClause(whereClause, whereClauseAdded));
        whereClauseAdded = true;
    }

    if (itemFlag <> 0){ // itemFlag can never be null. int's are either set or 0.
        whereClause = " a.item_flag = " + itemFlag;
        b.append(addWhereClause(whereClause, whereClauseAdded));
        whereClauseAdded = true;
    }

    if (itemIds != null && itemIds.size() > 0){
        String inList = "";
        for (Long id : itemIds){
            if (inList == null){
                inList = " " + id;
            else
                inList = ", " + id;
        }

        whereClause = " a.item_id in (" + inList + ")";
        b.append(addWhereClause(whereClause, whereClauseAdded));
    }   

    return b;

}

private void executeQuery(Connection connection, String itemType, int itemFlag, List<Long> itemIds) throws SQLException{

    PreparedStatement statement = connection.prepareStatement(this.generateQuery(itemType, itemFlag, itemIds));
    statement.executeQuery();

}

How do I include optional parameters using IF-THEN-ELSE logic in a SQL query?

Consider the following. If a parameter is NULL or empty, the default value will be the field in question

Select *
from dbo.test
where LessonName   = IsNull(NullIf(@LessonName,''),LessonName) 
 AND  StartDate    = IsNull(NullIf(@DateFrom,''),StartDate) 
 AND  EndDate      = IsNull(NullIf(@DateTo,''),EndDate)
 AND  LocationCode = IsNull(NullIf(@LocationCode,''),LocationCode)

Utilising optional parameters in where clause

If you're willing to sacrifice a tiny amount of time on each execution, OPTION(RECOMPILE) will provide the performance equal to dynamic SQL but without all the perils of it.

select * from table
WHERE
    (StartDate >= @StartDate or @StartDate is null) and
    (StartDate <= @EndDate or @EndDate is null) and
    (CE.ClientID = @ClientID or @ClientID is null)
option(recompile)

How can I use optional parameters in a T-SQL stored procedure?

Dynamically changing searches based on the given parameters is a complicated subject and doing it one way over another, even with only a very slight difference, can have massive performance implications. The key is to use an index, ignore compact code, ignore worrying about repeating code, you must make a good query execution plan (use an index).

Read this and consider all the methods. Your best method will depend on your parameters, your data, your schema, and your actual usage:

Dynamic Search Conditions in T-SQL by by Erland Sommarskog

The Curse and Blessings of Dynamic SQL by Erland Sommarskog

If you have the proper SQL Server 2008 version (SQL 2008 SP1 CU5 (10.0.2746) and later), you can use this little trick to actually use an index:

Add OPTION (RECOMPILE) onto your query, see Erland's article, and SQL Server will resolve the OR from within (@LastName IS NULL OR LastName= @LastName) before the query plan is created based on the runtime values of the local variables, and an index can be used.

This will work for any SQL Server version (return proper results), but only include the OPTION(RECOMPILE) if you are on SQL 2008 SP1 CU5 (10.0.2746) and later. The OPTION(RECOMPILE) will recompile your query, only the verison listed will recompile it based on the current run time values of the local variables, which will give you the best performance. If not on that version of SQL Server 2008, just leave that line off.

CREATE PROCEDURE spDoSearch
    @FirstName varchar(25) = null,
    @LastName varchar(25) = null,
    @Title varchar(25) = null
AS
    BEGIN
        SELECT ID, FirstName, LastName, Title
        FROM tblUsers
        WHERE
                (@FirstName IS NULL OR (FirstName = @FirstName))
            AND (@LastName  IS NULL OR (LastName  = @LastName ))
            AND (@Title     IS NULL OR (Title     = @Title    ))
        OPTION (RECOMPILE) ---<<<<use if on for SQL 2008 SP1 CU5 (10.0.2746) and later
    END

Optional Arguments in WHERE Clause

Alternatively to the ISNULL / COALESCE options, you can test the parameters for being null:

SELECT NAME  
FROM TABLE  
WHERE  
    (@City IS NULL OR City = @City)
AND 
    (@Gender IS NULL OR Gender = @Gender)
AND 
    (@Age IS NULL OR Age = @Age) 

Optional parameters in SQL query

If you just need to to paginate your query and return a specific range of results, you can simply use OFFSET FETCH Clause.

That way there is no need to filter result items by RowNumber. I think this solution is easier:

SELECT * 
FROM SHOP.dbo.PRODUCT
WHERE CATEGORY = 'ARDUINO' AND PRODUCTNAAM LIKE '%yellow%'
ORDER BY PRODUCTNUMMER
OFFSET 100 ROWS          -- start row
FETCH NEXT 20 ROWS ONLY  -- page size

Find out more Pagination with OFFSET / FETCH

SQL query with optional parameters in a related table

Something like this should work:

SELECT DISTINCT p.Name
FROM Person p
LEFT JOIN EventRegistration e
ON p.Id = e.PersonId
WHERE (@DateRangeStart IS NULL OR DateRegistered >= @DateRangeStart)
AND (@DateRangeEnd IS NULL OR DateRegistered <= @DateRangeEnd)

Optional parameter in where condition - How to increase performance - PL/SQL

Functionally, Coalesce is a great way to handle optional search criteria.
Performancewise it is not good. The optimiser will not be able to optimize efficiently since it decides on the execution plan only once, optimising for the search criteria provided the first time the SQL is run.

If different executions plans would be required to get acceptable performance then you need to use different SQL statements for those different search patterns.

Here are two different solutions that I have used in the past:

1. Create several different SQL:s where each handles a subset of the possible search criteria that will share the same execution plan. In your case you could have one you use when iAge is null, another when idaysold is null and a third when neither is null.

2. Use dynamic SQL.

---

Related Topics