SQL add filter only if a variable is not null

Question

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.

Answer 1

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

Answer 2

Here is a better way to handle: using LEN(ISNULL)... OR:

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