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.

Perhaps ... AND (route_query = @l_s_query OR @l_s_query IS NULL) ...? — jarlh
– jarlh, Commented Aug 8, 2016 at 11:40
add this AND (@l_s_query IS NULL OR route_query = @l_s_query) — Pirate X
– Pirate X, Commented Aug 8, 2016 at 11:42
Possible duplicate of use optional parameter in WHERE clause — Pirate X
– Pirate X, Commented Aug 8, 2016 at 11:50
The question in the title "is not null" disagrees with the question in the header "is non empty" (since we call zero-length strings "empty"). This is why you've got two different answers, from sagi and Tamas. — Steve Jessop
– Steve Jessop, Commented Aug 8, 2016 at 13:13
I've previously used a CASE statement in queries like this, with the value being the same in a false case eg. ... AND route_query = (CASE WHEN @l_s_query is not null THEN @l_s_query ELSE route_query END) — SeanR
– SeanR, Commented Aug 8, 2016 at 13:21

Martin Smith · Accepted Answer · 2016-08-08 18:55:29Z

28

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

edited Aug 8, 2016 at 18:55

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

answered Aug 8, 2016 at 11:41

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

jarlh Over a year ago

Second query is a bit different when it comes to NULL values in route_query.

sagi Over a year ago

@jarlh Yea.. I've added a comment about this:)

Cee McSharpface Over a year ago

what about performance implications? does this kind of expression not prevent index use, or are recent versions of mssql smart enough to account for that? I remember it was sometimes necessary to write two copies of such SELECTs, one with the parameter in the WHERE clause and one without, wrapped in an IF ... ELSE ... END

encryptoferia Over a year ago

COALESCE(@l_s_query,route_query) = route_query , just found out first hand, this can cause problem if the value is null and the search parameter is null, null = null does not evaluate to true it seems.

Jaydip Jadhav · Accepted Answer · 2016-08-08 14:37:16Z

4

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

answered Aug 8, 2016 at 14:37

Jaydip Jadhav

12.3k6 gold badges26 silver badges41 bronze badges

Comments

Eray Balkanli · Accepted Answer · 2018-02-09 21:38:53Z

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)

edited Feb 9, 2018 at 21:38

Eray Balkanli

8,05013 gold badges60 silver badges94 bronze badges

answered Nov 11, 2017 at 20:23

PavanT

818 bronze badges

Comments

Tamas Rev · Accepted Answer · 2016-08-09 08:09:20Z

1

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.

edited Aug 9, 2016 at 8:09

answered Aug 8, 2016 at 11:43

Tamas Rev

7,1745 gold badges37 silver badges52 bronze badges

4 Comments

xehpuk Over a year ago

Your query will return zero results if@l_s_query is NULL or empty.

Tamas Rev Over a year ago

Indeed, OR is the right fallback-operator. Fixed the code accordingly.

xehpuk Over a year ago

Your syntax is off. You are missing an operator, I think. IS EMPTY doesn't seem to exist, though. And if @l_s_query is non-empty, it isn't checked against route_query.

Tamas Rev Over a year ago

Rght, fixed them too. If @l_s_query is checked against route_query if and only if it isn't empty or null.

djv · Accepted Answer · 2016-08-08 19:21:16Z

0

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

edited Aug 8, 2016 at 19:21

djv

15.8k7 gold badges53 silver badges81 bronze badges

answered Aug 8, 2016 at 11:46

Clar Cleetus

2751 silver badge12 bronze badges

Collectives™ on Stack Overflow

SQL add filter only if a variable is not null

5 Answers 5

4 Comments

Comments

Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

Comments

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related