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