Posted by Tony Rogerson on 09/29/07 09:19

The proc below will take your CSV input and put it into a table #csv_split
which you can then extract each @d variable for.

Is there a specific reason you need @d1, @d2 etc... what are you trying to
do? It might be possible to do it set wise.

Tony.

CREATE PROC array_parse
@csv varchar(8000) = ',1,7,21,25,60'
AS
BEGIN

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

DECLARE @d1 varchar(3)
DECLARE @d2 varchar(3)
DECLARE @d3 varchar(3)
DECLARE @d4 varchar(3)
DECLARE @d5 varchar(3)
DECLARE @d6 varchar(3)
DECLARE @d7 varchar(3)

DECLARE @sql nvarchar(4000)

CREATE TABLE #csv_split (
pos int not null PRIMARY KEY IDENTITY,
data int not null
)

SET @sql = REPLACE( @csv, ',', CHAR(13) + CHAR(10) + 'insert #csv_split
( data ) values( ' )

SET @sql = REPLACE( @sql, CHAR(13), ' );' + CHAR(13) )

SET @sql = RIGHT( @sql, LEN( @sql ) - 5 ) + ' );'

EXEC( @sql )

SET @d1 = ( SELECT data FROM #csv_split WHERE pos = 1 )
SET @d2 = ( SELECT data FROM #csv_split WHERE pos = 2 )
SET @d3 = ( SELECT data FROM #csv_split WHERE pos = 3 )
SET @d4 = ( SELECT data FROM #csv_split WHERE pos = 4 )
SET @d5 = ( SELECT data FROM #csv_split WHERE pos = 5 )
SET @d6 = ( SELECT data FROM #csv_split WHERE pos = 6 )
SET @d7 = ( SELECT data FROM #csv_split WHERE pos = 7 )

SELECT @d1, @d2, @d3, @d4, @d5, @d6, @d7

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]


"bobc" <bcanavan@fmbnewhomes.com> wrote in message
news:1191015416.228609.59050@50g2000hsm.googlegroups.com...
> In my stored procedure, I want to parse @ArrayOfDays into @d1 through
> @d5.
>
> @ArrayOfDays is a varchar input parameter containing,
> for example, "1.7.21.25.60." - five elements.
>
> Most active vars:
> @i - loop counter
> @char - current char in string
> @tempVal - contains the current element as it is being built
> @tempValExecString - contains SELECT stmt for EXEC()
>
> I'm using EXEC() to execute a dynamically built SELECT.
>
> The error I get when calling from vb.net is:
> Must declare the variable '@tempVal'.
>
> Two manual traces indicate the logic is ok.
>
> I suspect my assignment statement for @tempValExecString.
>
> Any help would be appreciated. - BobC
>
> ----------------------------------------------------------
> DECLARE
> @d1 varchar(3),
> @d2 varchar(3),
> @d3 varchar(3),
> @d4 varchar(3),
> @d5 varchar(3),
> @i int,
> @char char(1),
> @tempVal varchar(3),
> @tempValExecString varchar(30)
>
> SELECT @tempVal = ''
> SELECT @i = 1
>
> WHILE @i < LEN(@ArrayOfDays)
> BEGIN
> SELECT @char = SUBSTRING(@ArrayOfDays, @i, 1)
> WHILE @char <> '.'
> BEGIN
> SELECT @tempVal = @tempVal + @char
> SELECT @char = SUBSTRING(@ArrayOfDays, @i+1, 1)
> IF @char = '.'
> BEGIN
> /* the following should produce "SELECT @d1 = 1" when it reads the
> first period(.) */
> SELECT @tempValExecString = 'SELECT @d' + LTRIM(RTRIM(STR(@i))) + '
> = @tempVal'
> EXEC(@tempValExecString)
> SELECT @tempVal = ''
> SELECT @i = @i + 1
> END
> SELECT @i = @i + 1
> END
> END
> ----------------------------------------------------------
>

[Back to original message]