SQL add filter only if a variable is not null

By | January 12, 2018
Questions:

Hi I have query as follows:

    SELECT  route_id [ROUTE_ID]
    FROM route_master(NOLOCK)
    WHERE  route_ou = 2
    AND   route_query = @l_s_query
    AND   lang_id  = 1

Here, the ” AND route_query = @l_s_query” condition in WHERE clause should be added only when @l_s_query is non empty. I donot want to write IF ELSE condition for @l_s_query. Is there any way to handle in WHERE clause itself directly.Thanks.

Answers:

You can translate your requirement into :

SELECT  route_id [ROUTE_ID]
FROM route_master(NOLOCK)
WHERE  route_ou = 2
AND   (@l_s_query is null OR route_query = @l_s_query)
AND   lang_id  = 1
OPTION (RECOMPILE)

The OPTION (RECOMPILE) is optional but can give better execution plans at the expense of extra compilation time as discussed in the canonical article on the topic Dynamic Search Conditions in T‑SQL

Or with COALESCE() to avoid the OR :

WHERE  route_ou = 2
AND   COALESCE(@l_s_query,route_query) = route_query 
AND   lang_id  = 1

Note: As @jarlh said, if route_query is nullable, this may cause some issues becuase of null comparison, so you may want to use the first query.

Another option of this is two separate queries using UNION ALL , one for each condition –

SELECT .. FROM .. 
WHERE @l_s_query IS NULL
UNION ALL
SELECT .. FROM .. 
WHERE @l_s_query = route_query

On terms of performance,only the last one will use the index, I believe the first one will be the fastest, but it may change depanding on the indexes, sizes of the tables ETC..

Questions:
Answers:

the ” AND route_query = @l_s_query” condition in WHERE clause should be added only when @l_s_query is non empty.

    WHERE  route_ou = 2
    AND   (  (@l_s_query IS NOT NULL AND route_query = @l_s_query ) OR
              @l_s_query IS NULL ) 
    AND   lang_id  = 1

Questions:
Answers:
SELECT  route_id [ROUTE_ID]
FROM route_master(NOLOCK)
WHERE  route_ou = 2
AND   (route_query = @l_s_query AND @l_s_query IS NOT NULL)
AND   lang_id  = 1

Questions:
Answers:

You can emulate if-else with an OR:

AND ((0 = len(@l_s_query) OR @l_s_query IS NULL OR route_query = @l_s_query)

Explanation:

  • AND (...) – the parentheses is required to apply the non-empty check only for this predicate
  • 0 < len(@l_s_query) – is a way to determine non-emptiness for varchar-s.
Questions:
Answers:

More better way to handle with an LEN(ISNULL)… OR:

AND (LEN(ISNULL(@l_s_query,”)) = 0 OR route_query = @l_s_query)

Leave a Reply

Your email address will not be published. Required fields are marked *