SQL query using LIKE operators for multi-valued parameters

Question

I have a dataset that has two fields Main_Activity and Requesting_Activity

Sample dataset"

Main_activity    Reuesting_Activity
-------------    ------------------
Act1             Act1,Act1a,Act2,Act3
Act2             Act1a,Act3,Act5
Act3             Act1,Act3,Act4
Act4             Act2,Act4,Act5

When I use the SQL query and pass Act1 as parameter (@mainActivity) for the dataset

SELECT DISTINCT
    Main_Activity,
    Requesting_Activity
FROM Activity_table
WHERE Requesting_Activity LIKE ''+ (@mainActivity) +'' OR Requesting_Activity LIKE ''+ (@mainActivity) +',%' OR Requesting_Activity LIKE '%,'+ (@mainActivity) +',%' OR Requesting_Activity LIKE '%,'+ (@mainActivity) +''

I get the expected output

Act1
Act3

The problem is when I make the parameter as multi-select, I am getting error. How do I resolve it? How to use "LIKE and "IN" operator in same query?

have a look here: stackoverflow.com/questions/1865353/… its already a duplicate — deterministicFail
– deterministicFail, Commented Mar 6, 2015 at 7:27
I already had a look at that post, but there the values to be matched are static. In my question it is dynamic. — damseldeebi
– damseldeebi, Commented Mar 6, 2015 at 7:30
@damseldeebi your problem is not in SSRS, but in your table design. Multivalue fields are wrong! You are trying to solve a problem in SSRS, when you just need to redesign your table(s). — mordack550
– mordack550, Commented Mar 6, 2015 at 7:40
@damseldeebi I'm sorry, but you are doing it completely wrong. Why do you even care about number of rows, when you are spending A LOT of processing power on LIKEs on multi value fields?? Are you short of hard drive space? Few thousand more records? That's nothing for almost any DMBS, even less on engines like SQL Server that can handle datawarehouses with tables with 30+millions records. — mordack550
– mordack550, Commented Mar 6, 2015 at 9:04
@damseldeebi you are facing the X-Y problem. You are trying to solve "How to retrieve the rows I want" when the only thing your should care about is "How to correctly design a relational database". Multivalues field in a relational database are wrong. Always, no exception. — mordack550
– mordack550, Commented Mar 6, 2015 at 9:08

A ツ · Accepted Answer · 2015-03-06 09:42:27Z

1

your where clause is full of redundancies

WHERE Requesting_Activity LIKE '%'+ (@mainActivity) +'%'

answered Mar 6, 2015 at 9:42

A ツ

1,2572 gold badges9 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

SQL query using LIKE operators for multi-valued parameters

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related