How to execute a SP using a dynamic range in tsql

Question

I have this tsql/sp...

declare @IdRange NVARCHAR(255)

SET @IdRange = '1,2,4'

select 
    FeatureName
FROM
    Feature F
    JOIN GroupFeature GF ON GF.FeatureId = F.FeatureId 
WHERE 
    GroupId IN ( @IdRange)

The value for @IdRange can vary.

This, when executed will fail.

I could use dynamic SQL and execute it all as string but I just want to explore if there is an alternative?

I have now tried this:

declare @IdRange NVARCHAR(255)

SET @IdRange = '1,2,4'
select 
    FeatureName
FROM
    Feature F
    JOIN GroupFeature GF ON GF.FeatureId = F.FeatureId 
WHERE 
    CHARINDEX(',' + GroupId + ',', ',' + @IdRange + ',') <> 0

and get this:

Argument data type int is invalid for argument 1 of charindex function.

score 2 · Accepted Answer · answered Jul 07 '14 at 09:47

2

something like:

WHERE
    CHARINDEX(',' + cast(GroupId as varchar) + ',', ',' + @IdRange + ',') <> 0

answered Jul 07 '14 at 09:47

tschmit007

7,559
2
35
43

Hi, thanks for your suggestion. I have edited my question to show my latest attempt based on your suggestion. It is possible that I am not understanding what you are suggestion though :) – Andrew Simpson Jul 07 '14 at 09:54
lol, I just realised myself! This is what happens when you are stuck in traffic for 2 hours going into work!. Thanks it worked! – Andrew Simpson Jul 07 '14 at 09:56
Hi, just realized that this reorders my results by GroupId. So in my example where the args are 1.2.4 (and I add the GroupId onto the select bit) it returns correctly. If I change the args to 1.4.2 it rearranges it as 1.2.4. Is there a way I can stop this? Thanks – Andrew Simpson Jul 07 '14 at 11:57

score 1 · Answer 2 · edited May 23 '17 at 10:32

For deeper study and understanding: I think that this T-SQL stored procedure that accepts multiple Id values responds to your question the best.

As for short answer: The reason is, that IN operator corresponds to a set, it doesnt work for a string...

Alternatives for you:

WHERE ','+@IdRange+',' like + '%,'+GroupId+',%'

or WHERE CHARINDEX(',' + GroupId + ',', ',' + @IdRange + ',') > 0 this adds leading and ending comma to the IdRange string and then uses the like operator to search for records. The pros: Simple to write and understand, Cons: Performance for big datasets and unable to use any kind of index

I wont talk about changeing the type of input to the SP (ie a table variable or temporal table), another alternative,especially if the Feature table contains a lot of records would be to: 1) parse the @IdRange into a temp table 2) utilize join/intersect

to split, read this T-SQL: Opposite to string concatenation - how to split string into multiple records

and then just select from Feature table joining on the temp table

Have a nice day

HI, thanks for your insight. Much appreciated. I need to look into all that you said and wil lreport back. Thanks — Andrew Simpson, Jul 07 '14 at 09:51

How to execute a SP using a dynamic range in tsql

2 Answers2