Execute sql stored procedure with array input from kettle table input step

Question

I am having a sql stored procedure that takes 2 inputs and returns a resultset based on the given input. I am using the input1 in sql IN clause like,

WHERE myCol IN(@input1)

when executing the store procedure from kettle table input step, If i give a single value for the input1, it works fine.

EXEC sp_procedureName @input1='07423', @input2='2014-09-02'

If i give multiple values like below, it results empty resultset.

EXEC sp_procedureName @input1='07423,07022,07033', @input2='2014-09-02'

How can i pass multiple values as a parameter to my procedure. the transformation will execute the procedure and insert the result set into another table using table output step.

Answer 1

What you need to do inside your stored procedure is split the comma delimited list of numbers into a list of single values in rows. Once you have this you can use WHERE myCol IN(SELECT v FROM @r), or use an INNER JOIN instead of IN. One point you might have to watch for is that you have leading zeros - if you want to keep those you will need to use strings instead of integers.

There are lots of articles around on the subject of splitting delimited values into rows, you could start here.