It's an old problem in stored procs, and programmers have come up with various hacks to do it. Here is a message post from Joe Celko, one of the top SQL Server gurus in the industry...
~~
Passing a list of parameters to a stored procedure 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 also need a table called Sequence, which is a set of integers from 1 to (n).
SELECT keycol,
CAST (SUBSTRING (',' + I1.input_string + ',', MAX(S1.seq + 1),
(S2.seq - MAX(S1.seq + 1)))
AS INTEGER),
COUNT(S2.seq) AS place
FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
WHERE SUBSTRING (',' + I1.input_string + ',' , S1.seq, 1) = ','
AND SUBSTRING (',' + I1.input_string + ',' , S2.seq, 1) = ','
AND S1.seq < S2.seq
AND S2.seq <= DATALENGTH(I1.input_string) + 1
GROUP BY I1.keycol, I1.input_string, S2.seq
ORDER BY I1.keycol, I1.input_string, S2.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 left hand comma of the bracketing pair is the closest one to the second comma. The place column tells you the relative position of the value in the input string
--CELKO--
Joe Celko, SQL Guru
~~~
And here is a link to a page with several descriptions of workarounds:
http://vyaskn.tripod.com/passi..._stored_procedures.htm
And here is a simpler way to do it using XML fragments if you are running against SQL Server 2005:
http://weblogs.asp.net/dwahlin...n-sql-server-2005.aspx
