CSV Field Stored Procedure

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?
--=20
Keith
"Steve" <steve@.nospam.com> wrote in message =
news:empTbYQVEHA.1164@.tk2msftngp13.phx.gbl...
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 =3D '%' + @.delimeter + '%'
declare @.separator_position int=20
declare @.array_value varchar(1000)=20
=20
set @.input =3D @.input + ','
=20
while patindex(@.separator , @.input) <> 0=20
begin
=20
select @.separator_position =3D patindex(@.separator , @.input)
select @.array_value =3D left(@.input, @.separator_position - 1)
=20
Insert @.IntTable
Values (@.array_value)
select @.input =3D 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...xstring_multi, and the entire article http://www.sommarskog.se/arrays-in-sql.html may also be useful.
Steve Kass
Drew University
"Steve" <steve@.nospam.com> wrote in message news:empTbYQVEHA.1164@.tk2msftngp13.phx.gbl...
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@.nospam.com> wrote in message news:empTbYQVEHA.1164@.tk2msftngp13.phx.gbl...
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