Pass a list of values as a parameter to stored procedure

I encountered this problem long time ago and I used some complex approach to solve it. Today I come across a excellent approach by converting delimited string into a table return as an table-value function.

The original article is here:

http://www.codeproject.com/cs/database/TableValuedFnsAsArrays.asp

The function is as below.

CREATE FUNCTION [dbo].[StringToTable]
(
@string VARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(
data VARCHAR(256)
)
BEGIN

DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)

WHILE @start <= LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1

INSERT INTO @output (data)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END

RETURN

END