Pass an Array into a Stored Procedure

CREATE PROCEDURE [spListToTable]
@vcList VARCHAR(8000),
@vcDelimiterVARCHAR(8000),
@TableName  SYSNAME,
@ColumnNameSYSNAME
AS
SET NOCOUNT ON

DECLARE@iPosStartINT,
@iPosEndINT,
@iLenDelimINT,
@iExitINT,
@vcStrvarchar(8000),
@vcSqlvarchar(8000)

SET @iPosStart = 1
SET @iPosEnd = 1
SET @iLenDelim = LEN(@vcDelimiter)

SET @vcSql = 'INSERT ' + @TableName + ' (' + @ColumnName + ') VALUES ('''

SET @iExit = 0

WHILE @iExit = 0
BEGIN
SET @iPosEnd = CHARINDEX(@vcDelimiter, @vcList, @iPosStart)

IF @iPosEnd <= 0
BEGIN
SET @iPosEnd = LEN(@vcList) + 1
SET @iExit = 1
END

SET @vcStr = SUBSTRING(@vcList, @iPosStart, @iPosEnd - @iPosStart)

EXEC(@vcSql + @vcStr + ''')')

SET @iPosStart = @iPosEnd + @iLenDelim
END

RETURN 0
GO

-- You can use it like:

DECLARE @vcArray VARCHAR(255)
SELECT @vcArray = '1|2|3|4|5|6|7|8|9|10'

CREATE TABLE #values (v INT NOT NULL)

EXEC spListToTable @vcArray, '|', '#values', 'v'

CREATE TABLE #SomeTable (Status VARCHAR(10), Value INT)

INSERT INTO #SomeTable (Status, Value) SELECT 'New', v FROM #values

SELECT * FROM #SomeTable

DROP TABLE #values
DROP TABLE #SomeTable