CSV Arrays and Stored Procedures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

CSV Arrays and Stored Procedures

Author

Topic

jackstow
Posting Yak Master

160 Posts

Posted - 2001-07-20 : 12:21:20

OK this is an old chestnut I guess from looking at the postings, but I just can't get it to work. Here's my code:

CREATE PROCEDURE insert_forecast_temp_table
@player_id int,
@forecast_array varchar (1000)

AS
DECLARE
@separator char(1),
@separator_position int,
@array_value varchar(1000)

create table dbo.temp_forecasts_table (temp_id int identity primary key not null, temp_value int not null)

set nocount on

select separator = '-'
set @forecast_array = @forecast_array + @separator

while patindex('%' + @separator + '%' , @forecast_array) <> 0
begin

-- patindex matches the a pattern against a string
select @separator_position = patindex('%' + @separator + '%' , @forecast_array)
select @array_value = left(@forecast_array, @separator_position - 1)

-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @array_value holds the value of this element of the array
--select Array_Value = @array_value
INSERT INTO dbo.temp_forecasts_table (temp_value) VALUES (@array_value)

-- This replaces what we just processed with and empty string
select @forecast_array = stuff(@forecast_array, 1, @separator_position, '')
end

set nocount off
DROP TABLE dbo.temp_forecasts_table
GO

The temp table is created fine and I can insert into it, but when the insert statement is placed within the loop which should parse the string ( in the format of 1-0-0-2-3-4-4-10-3-5-7-7-) nothing is inserted..
Any ideas..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources