Three is no need to use proprietary procedural code to pass a list of
parameters to a stored procedure. It can be done by putting them into
a string with a separator. I like to use the traditional comma.
Let's assume that you have a whole table full of such parameter lists:
CREATE TABLE InputStrings
(keycol CHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL);
INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
...
This will be the table that gets the outputs, in the form of the original
key column and one parameter per row.
CREATE TABLE Parmlist
(keycol CHAR(5) NOT NULL PRIMARY KEY,
parm INTEGER NOT NULL);
It makes life easier if the lists in the input strings start and end
with a comma. You will need a table of sequential numbers -- a standard
SQL programming trick, Now, the real query:
INSERT INTO ParmList (keycol, parmlist)
SELECT keycol,
CAST (SUBSTRING (I1.input_string
FROM S1.seq +1
FOR MIN(S2.seq) - S1.seq -1)
AS INTEGER)
FROM InputStrings AS I1 , Sequence AS S1 , Sequence AS S2
WHERE SUBSTRING (‘,’ || I1.input_string || ‘,’ FROM S1.seq FOR 1) = ‘,’
AND SUBSTRING (‘,’ || I1.input_string || ‘,’ FROM S2.seq FOR 1) = ‘,’
AND S1.seq < S2.seq
GROUP BY I1.keycol, I1.input_string, S1.seq;
The S1 and S2 copies of Sequence are used to locate bracketing pairs of
commas, and the entire set of substrings located between them is
extracts and cast as integers in one non-procedural step. The trick
is to be sure that the right hand comma of the bracketing pair is the
closest one to the first comma.
You can then write:
SELECT *
FROM Foobar
WHERE x IN (SELECT parm FROM Parmlist WHERE key_col = :something);