Using "IN" operator in a dynamic query

Question

i've been searching SO for a while and found nothing related to this.

We use heavily the dynamic approach for most of our queries like this:

Declare @ContactId VarChar(8000)

Select @ContactId = '1'

Select * 
From Person.Contact
Where 1 = 1 And 
Case When Len(Ltrim(Rtrim(@ContactId))) = 0 Then 1 Else ContactID End =
Case When Len(Ltrim(Rtrim(@ContactId))) = 0 Then 1 Else @ContactId End

This way the query gets filtered dynamically if there's a value on the parameter

But the problem comes when trying the same stuff with several IDs like so:

Declare @ContactId VarChar(8000)

Select @ContactId = '1,2,3'

Select * 
From Person.Contact
Where 1 = 1 And 
Case When Len(Ltrim(Rtrim(@ContactId))) = 0 Then 1 Else ContactID End In (
Select Case When Len(Ltrim(Rtrim(@ContactId))) = 0 Then 1 Else ( Select id From dbo.SplitString(@ContactId,',') ) End )

Sql throws an error "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

that's totally normal and expected, my question is:

Is there a way to dynamically do this kind of filtering ?

The solution we use is this:

Declare @Table Table(
    Col1    Int,
    Col2    Int,
    Col3    Int
)

Insert @Table
Select Col1, Col2, Col3
From Person.Contact
Where [many filters except the in clause filters]

If Len(Ltrim(RTrim(@ContactId))) > 0
    Select * 
    From @Table
    Where ContactId In ( Select Id From dbo.SplitString(@ContactId, ',') )
Else 
Select *
From @Table

But it's not an option when the query is massive and feeding a table variable is overkill for this. Hope i made my point and someone is kind enough to help me find a solution to this.

PS: Using sp_ExecuteSql is not an option in this scenario either, sorry.

If you rule out the two ways that you would do something, then yes, you're going to have a hard time. — Jim
– Jim, Commented Oct 18, 2012 at 15:38

podiluska · Accepted Answer · 2012-10-17 21:41:13Z

1

How you should do this is with a table parameter.

But if you want to persist in this approach.

Declare @ContactId VarChar(8000) 

Select @ContactId = '1,2,3'
Select *  
From Person.Contact 
where ',' + @contactID + ',' like '%,'+convert(varchar(50),contactid)+',%'

answered Oct 17, 2012 at 21:41

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

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

6 Comments

Sam Ccp Over a year ago

This approach is interesting enough, but the sad part is that it's not selecting anything if the parameter is empty :(

podiluska Over a year ago

@SamCcp add an or len(@contactID)=0

Sam Ccp Over a year ago

Ok, now we're getting somewhere, by adding that condition, it does bring the desired data even when the parameter is empty, but i can't play with many conditions like this right? I mean how could i search for another condition say... @EmailPromotion = '1,2'

podiluska Over a year ago

@SamCcp Sure you can. Stick ( around the like / or ) and add as many other filters as you like. eg: and (','+@emailpromotion+',' like '%,' + emailpromotion + ',%')

Sam Ccp Over a year ago

Yeah, i just figured it out, thanks, i'll run some queries and test the performance, in the meantime, you sir earned a fan. Thanks.

|

rs. · Accepted Answer · 2012-10-17 21:48:22Z

1

Try this

Select * 
From Person.Contact
Where (
           len(@contactid) > 0 AND 
           ContactID IN (Select id From dbo.SplitString(@ContactId,','))) 
or 1 = 1

edited Oct 17, 2012 at 21:48

answered Oct 17, 2012 at 21:40

rs.

27.6k13 gold badges71 silver badges93 bronze badges

2 Comments

Sam Ccp Over a year ago

Same stuff than previous answer, it doesn't display anything if the parameter is empty and the OR part must be omitted because it brings all the rows on the table.

Reejesh PK Over a year ago

def of SplitString stackoverflow.com/a/10914602/7710739

Collectives™ on Stack Overflow

Using "IN" operator in a dynamic query

2 Answers 2

6 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related