Hi All,

I would like to submit a large amount of CSV text into a stored procedure. At present I'm using a varchar(8000) parameter and then converting this into a temporary table using something like the sql below. My question is does anybody have any suggestions on how to get round the 8000 limit without doing round trips?

thanks

Steve

declare @separator char(3)

set @separator = '%' + @delimeter + '%'

declare @separator_position int
declare @array_value varchar(1000)

set @input = @input + ','

while patindex(@separator , @input) <> 0
begin

select @separator_position = patindex(@separator , @input)
select @array_value = left(@input, @separator_position - 1)

Insert @IntTable
Values (@array_value)

select @input = stuff(@input, 1, @separator_position, '')
end

Multiple parameters?

--
Keith


"Steve" <steve (AT) nospam (DOT) com> wrote

Hi All,

I would like to submit a large amount of CSV text into a stored procedure. At present I'm using a varchar(8000) parameter and then converting this into a temporary table using something like the sql below. My question is does anybody have any suggestions on how to get round the 8000 limit without doing round trips?

thanks

Steve

declare @separator char(3)

set @separator = '%' + @delimeter + '%'

declare @separator_position int
declare @array_value varchar(1000)

set @input = @input + ','

while patindex(@separator , @input) <> 0
begin

select @separator_position = patindex(@separator , @input)
select @array_value = left(@input, @separator_position - 1)

Insert @IntTable
Values (@array_value)

select @input = stuff(@input, 1, @separator_position, '')
end

Steve,

You can pass the CSV text as a parameter of type text or ntext, and use PATINDEX and SUBSTRING to parse it. An alternative, that if possible will make parsing the long parameter much easier is to pass it as non-separated text with a fixed-length field width instead of comma-separated. You can find an example of this technique at http://www.sommarskog.se/arrays-in-s...ixstring_multi, and the entire article http://www.sommarskog.se/arrays-in-sql.html may also be useful.

Steve Kass
Drew University
"Steve" <steve (AT) nospam (DOT) com> wrote

Hi All,

I would like to submit a large amount of CSV text into a stored procedure. At present I'm using a varchar(8000) parameter and then converting this into a temporary table using something like the sql below. My question is does anybody have any suggestions on how to get round the 8000 limit without doing round trips?

thanks

Steve

declare @separator char(3)

set @separator = '%' + @delimeter + '%'

declare @separator_position int
declare @array_value varchar(1000)

set @input = @input + ','

while patindex(@separator , @input) <> 0
begin

select @separator_position = patindex(@separator , @input)
select @array_value = left(@input, @separator_position - 1)

Insert @IntTable
Values (@array_value)

select @input = stuff(@input, 1, @separator_position, '')
end

Many thanks for your help guys.

Regards

Steve
"Steve" <steve (AT) nospam (DOT) com> wrote

Hi All,

I would like to submit a large amount of CSV text into a stored procedure. At present I'm using a varchar(8000) parameter and then converting this into a temporary table using something like the sql below. My question is does anybody have any suggestions on how to get round the 8000 limit without doing round trips?

thanks

Steve

declare @separator char(3)

set @separator = '%' + @delimeter + '%'

declare @separator_position int
declare @array_value varchar(1000)

set @input = @input + ','

while patindex(@separator , @input) <> 0
begin

select @separator_position = patindex(@separator , @input)
select @array_value = left(@input, @separator_position - 1)

Insert @IntTable
Values (@array_value)

select @input = stuff(@input, 1, @separator_position, '')
end