SQL query to filter data with 3 optional Parameter on same Column

Question

Suppose My table has a column ProfileID & i want to filter with ProfileID. In stored procedure i am sending @PageID, @GroupID, @ChannelID (all 3 parameters are optional, if not sent then these value will be 0 by default) to fileter out the data which are actually ProfileID.So actually i want to filter data with WHERE ProfileID IN(@PageID,@GroupID,@ChannelID ) but if all 3 parameters are 0 then i won't filter anything.I would filter if & only if atleast 1 paramters is >0

TABLE STRUCTURE

+--------+-----------------+---------------------+
| PostID | PostDescription | PostType  ProfileID |
+--------+-----------------+---------------------+
|  25151 | Messed up       | S         117       |
|  25152 | Messed up       | S         116       |
|  25153 | Messed up       | S         119       |
|  25154 | Messed up       | S         11        |
|  25155 | Messed up       | S         16        |
|  25157 | Messed up       | S         23        |
|  25158 | Messed up       | S         22        |
|  25159 | Messed up       | S         7         |
|        |                 |                     |
+--------+-----------------+---------------------+

Example Case

@PageID=117 ,@GroupID=116 , @ChannelID=16 should return rows with profileID IN(117,116,16)
@PageID=0 ,@GroupID=0, @ChannelID=11 should return rows with profileID =11
@PageID=117,@GroupID=0, @ChannelID=11 should return rows with profileID IN(117,11)
@PageID=0,@GroupID=0, @ChannelID=0 should return all rows without filtering.

How can i achieve this with WHERE condition ?

Add all these parameters and check if its more than 0 in where clause — Ven
– Ven, Commented Jan 19, 2018 at 9:38
I guess they all are int data types, so where @PageID+@GroupID+@ChannelID>0 — Ven
– Ven, Commented Jan 19, 2018 at 9:40

HoneyBadger · Accepted Answer · 2018-01-19 09:40:14Z

2

Simply use an or:

WHERE ProfileID IN(@PageID,@GroupID,@ChannelID )
   OR COALESCE(@PageID,@GroupID,@ChannelID) is null

Above if parameters are null, if they are 0:

WHERE ProfileID IN(@PageID,@GroupID,@ChannelID )
   OR (@PageID = 0 AND @GroupID = 0 AND @ChannelID = 0)

edited Jan 19, 2018 at 9:40

answered Jan 19, 2018 at 9:38

HoneyBadger

15.2k3 gold badges36 silver badges50 bronze badges

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

1 Comment

Sourav Mukherjee Over a year ago

Its 0 not NULL. Thanks its working i think.Ops it was so easy but i was so confused what to do. lol.

Collectives™ on Stack Overflow

SQL query to filter data with 3 optional Parameter on same Column

Example Case

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Example Case

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related