http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Madhivanan

From my blog:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/08/05/passing-an-array-csv-to-a-stored-procedure-with-data-validation-no-loops-no-self-joins-just-replace.aspx

exec csv_eg @csv = ',11,12,13,14,,,,,,,,,,,,,,12,',
@is_allow_nulls = 'Y',
@is_unique_values = 'N',
@is_debug = 'N'



CREATE PROC csv_eg
@csv varchar(500),
@is_allow_nulls char(1) = 'Y',
@is_unique_values char(1) = 'N',
@is_debug char(1) = 'N'
AS
BEGIN
/***
Convert a CSV to a TABLE with data validation

Tony Rogerson, Aug 2007

***/

SET @csv = ltrim(rtrim(@csv))

IF PATINDEX( '%[^,0-9]%', @csv ) > 0 -- Checks to make sure input is
digits or comma to prevent SQL injection
BEGIN
RAISERROR( 'Injection attempt or invalid data.', 16, 1 )
RETURN

END
-- Initial parse of input to make sure general syntax of the csv is
valid

-- check we have commas correct

IF LEFT( @csv, 1 ) <> ',' -- If not front comma then add one
SET @csv = ',' + @csv
IF RIGHT( @csv, 1 ) = ',' -- If last character is a comma then
add a NULL after it
SET @csv = @csv + 'NULL'

SET @csv = REPLACE( @csv, ',,', ',NULL,' ) -- Change ,, to NULL
SET @csv = REPLACE( @csv, ',,', ',NULL,' ) -- Need to do it twice for
rest of ,, not caught in first one

-- // initial parse is over.

-- Now the donkey work, convert it from a flat CSV structure to a TABLE
-- Simple string replacing, no need for self joins and loops

DECLARE @sql varchar(max)

SET @sql = REPLACE( @csv, ',', CHAR(13) + CHAR(10) + 'insert #csv_split
( csv_value ) values( ' )
SET @sql = REPLACE( @sql, CHAR(13), ' ); @V' + CHAR(13) )
SET @sql = RIGHT( @sql, LEN( @sql ) - 7 ) + ' ); @V'

SET @sql = REPLACE( @sql, '@V', '
if @@error <> 0 goto err;' )

-- We now have the SQL that will convert CSV to a TABLE, so exec it.
CREATE TABLE #csv_split (
val_position smallint not null IDENTITY,
csv_value int null CHECK( csv_value between 10 and 20 )
)

IF @is_allow_nulls = 'N'
EXEC( 'ALTER TABLE #csv_split ALTER COLUMN csv_value INT NOT NULL' )
IF @is_unique_values = 'Y'
EXEC( 'CREATE UNIQUE INDEX ncui ON #csv_split( csv_value )' )

SET @sql = 'SET NOCOUNT ON; ' + @sql + ';
goto done;
err:
DECLARE @msg varchar(120);
SET @msg = ''Data Error on value position '' + CAST( ( SELECT COUNT(*)
FROM #csv_split ) + 1 AS varchar(5) ) + '', processing terminated.'';
RAISERROR( @msg, 16, 1 );
done:'

IF @is_debug = 'Y'
PRINT @sql

EXEC( @sql )

IF @@ERROR <> 0
RETURN

SELECT *
FROM #csv_split

END

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]


[quoted text, click to view]