Run SP multiple times with varying parameter counts

Question

I have a stored procedure, the main part of it is a select query that looks like this:

SELECT 
    Col_A, Col_B,
    Col_C, Col_D,
    ...,
FROM
    TableName
WHERE
    Col_C = 'this' OR Col_C = 'that' OR Col_C = 'Other'

Now what if I wanted to change the values in the WHERE clause and have them be populated via @Parameter ? If this was an ideal situation, I could simply do:

WHERE Col_C = @Parameter

But, each time this stored procedure is run, the number of possibilities that Col_C could be will vary from 1 to 15 different options. I don't really think that having 15 parameters and then checking to see if each parameter has a value and then comparing that value to the column is the best idea, so I am reaching out to the community for ideas as to what I may be overlooking.

I was wondering if there was a way for me to do something along the lines of:

EXEC @return_value = [dbo].[BM-GetWeeklyEfficiencyInformation]
     @Start_Date = ...
     @End_Date = ...
     @ResourceGroupList = 'OPTION1 OPTION2 OPTION3 OPTION4 OPTION5 ...'

SELECT @return_value

Then, in the WHERE clause in the SP, compare it to OPTION1, then OPTION2, OPTION3, and so on.

I assume this can be done in one way or another. I just don't know how to begin.

Possible duplicate of [Parameterize an SQL IN clause](https://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) — Jeroen Mostert, Jul 24 '19 at 10:45

score 0 · Accepted Answer · answered Jul 24 '19 at 10:56

0

You can try this - Below query depicts how you can parameterize yours IN Clause.

SELECT 
       Col_A,
       Col_B,
       Col_C,
       Col_D,
       ...,
FROM
       TableName
WHERE
       Col_C in( select value from  STRING_SPLIT('this,that,Other',','))

Note: STRING_SPLIT function available in SQL Server 2016 or later, if you are using below SQL server 2016 then you can write custom split function.

answered Jul 24 '19 at 10:56

Mukesh Arora

1,763
2
8
19

I tweaked it a little bit to work with the parameter being passed: (SELECT value FROM string_split(@ResourceGroup, ',')) ... that works like a treat. Thank you, @mukesh-arora – Jason H. Jul 24 '19 at 11:07

Hardik Leuwa · Answer 2 · 2019-07-24T11:25:41.640

0

If you can pass all Options with comma separated then you can use below query

SELECT 
       Col_A,
       Col_B,
       Col_C,
       Col_D,
       ...,
FROM
       TableName
WHERE
 ',' + 'this,that,Other' + ',' Like  '%,' + lTrim(rTrim(Col_C)) + ',%'

DEMO

edited Jul 24 '19 at 11:25

answered Jul 24 '19 at 11:01

Hardik Leuwa

3,282
3
14
28

Run SP multiple times with varying parameter counts

2 Answers2