Arrays in a SP

[rohit]

hi, i'm passing a string to a Stored procedure(sp).this is string is delimited, say,
mon:tue:wed..is there a way in a sp to parse this string out an store it in an array and then use a for i=i to..whatever.. loop to access each element?

thanks

rohit

[Jane]

Hi -
You can't do this in SQL Server. There are no arrays, and the for i=i loop does not exist.
The way I approach something like this is to have a table with two columns. session id and stringval. In my front end I would insert the rows that I need using the sessionid (@@spid, mon), (@spid, tue) etc..
I would then pass the sessionid to the stored procedure and base my loop on the rows in the table for this sessionid. Cleanup the table on exiting the procedure.
There may be a better way but this works for me...

------------
rohit at 10/13/99 11:17:03 AM

hi, i'm passing a string to a Stored procedure(sp).this is string is delimited, say,
mon:tue:wed..is there a way in a sp to parse this string out an store it in an array and then use a for i=i to..whatever.. loop to access each element?

thanks

rohit

[Deepak Nayak]

There is no array support in MSSQL 6.5 and I am not aware if it is in MSSQL 7
But you can create a temp table which can act as an array
Here is a piece of code which will do exactly what you wanted in your problem

These are just TSQL statements which you may use in a stored procedure

Declare @array varchar(255),
@s1 varchar(255),
@start_pos int,
@end_pos int,
@len_gth int,
@loop int

Select @array = 'mon:tue:wed:thu:fri:sat:sun:'

Select @len_gth = len(@array) , @start_pos = 1 , @loop = 0

Create table #array ( in_dex int , val_ue varchar(255) )

while @start_pos < @len_gth - 1
Begin
Select @loop = @loop + 1

Select @end_pos = CHARINDEX ( &#39;:&#39; , SUBSTRING ( @array , @start_pos , @len_gth ))

insert into #array values (@loop , SUBSTRING ( @array , @start_pos , @end_pos - 1 ))

Select @start_pos = @start_pos + @end_pos

End

Select * from #array

Hope this helps you.

All the best



------------
Jane at 10/13/99 11:37:30 AM

Hi -
You can&#39;t do this in SQL Server. There are no arrays, and the for i=i loop does not exist.
The way I approach something like this is to have a table with two columns. session id and stringval. In my front end I would insert the rows that I need using the sessionid (@@spid, mon), (@spid, tue) etc..
I would then pass the sessionid to the stored procedure and base my loop on the rows in the table for this sessionid. Cleanup the table on exiting the procedure.
There may be a better way but this works for me...

------------
rohit at 10/13/99 11:17:03 AM

hi, i&#39;m passing a string to a Stored procedure(sp).this is string is delimited, say,
mon:tue:wed..is there a way in a sp to parse this string out an store it in an array and then use a for i=i to..whatever.. loop to access each element?

thanks

rohit