Pass an Array into a Stored Procedure

,

Have you ever wished T-SQL allowed you to pass an array into a stored procedure? Well the spListToTable stored procedure listed below accepts a delimeted string and turns it into a table.

Code your stored procedure to accept a varchar, create a temporary table, call spListToTable, and use the populated temporary table as if it were an array of values.

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

	DECLARE	@iPosStart	INT,
		@iPosEnd	INT,
		@iLenDelim	INT,
		@iExit		INT,
		@vcStr		varchar(8000),
		@vcSql		varchar(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

Rate

4.25 (4)

Share

Share

Rate

4.25 (4)