SQL add filter only if a variable is not null

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

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 –

WHERE @l_s_query IS NULL
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..

Leave a Comment