Building a a List for IN predicate of a query in a Stored Procedure

[bnp1]

I want to pass a number of Integers into a Stored Procedure that I can use in a query of the stored procedure. I am trying to figure out the best way to do this.
The Query is something like this:
Select * from xtable where id in (IDList)

I want to be able to pass 1-x ID's into the Stored Procedure.
I would love to be able to Do a CALL SPx(123,222,444,555)
and place that in the IDList, however th eIDList has to be a list of Integers. Or I could have a separate IN parameter for each possible ID and combine them, but I am not sure how to combine thme into a single element (IDList) withth ecorrect data type. I also may have a variable number of IDs I want to pass so I don't really want to have 100 IN paramenters when I may only enter two.

I hope this is clear. thanks for any help

 

[GhostWolf]

You might try defining a string parameter for your stored procedure. Then you could do a CALL SPx '123,222,444,555'.

 

[bnp1]

OK Once I pass the String in to the SP how do I create the IN predicate within the query since it is expecting a list of INTegers? So if I passed '123,222,333' in how can I get th evalues into the query:
select * from tablex where id in (xxx)

 

[GhostWolf]

Use the select statement from your example as the stored procedure - including the parentheses.

Your calling program will issue the Call SPx '123,222,333' statement. The SP doesn't receive the "quotes", only the string they enclose.

If the parameter's name is IDList, and the Select statement reads as in your example, what's executed is:
select * from xtable where id in (123,222,333)

That the parameter was a string means nothing to the query translator. As long as the ID numbers are numeric, it's receiving a valid statement.