CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Sourav Mukherjee
Sourav Mukherjee

Reputation: 71

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

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

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

How can i achieve this with WHERE condition ?

Upvotes: 0

Views: 1193

Answers (1)

HoneyBadger
HoneyBadger

Reputation: 15140

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)

Upvotes: 2

Related Questions