large arrays, UDFs and the text datatype

[matthew.weiner]

I have a bunch of SPs that all rely on a UDF that parses a comma
delimitted list of numbers into a table. Everything was working fine,
but now my application is growing and I'm starting to approach the 8000
character limit of the varChar variable used to store the list.

I would like to change the UDF only and avoid having to dig through all
of my stored procedures. I was hoping to use the text datatype to
allow for much larger lists, but I am unable to perform any
manipulations necessary to parse the list into a table. I have tried
PATINDEX, but it alone is not enough without the text maniuplations and
I don't think the sp_xml_preparedocument can be used in a UDF.

Anyone with any thoughts on managing large arrays in t-sql?
thanks,

Matt Weiner

[Dan Guzman]

Check out Erland's article at http://www.sommarskog.se/arrays-in-sql.html.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<ma************@med.nyu.edu> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

I have a bunch of SPs that all rely on a UDF that parses a comma
delimitted list of numbers into a table. Everything was working fine,
but now my application is growing and I'm starting to approach the 8000
character limit of the varChar variable used to store the list.

I would like to change the UDF only and avoid having to dig through all
of my stored procedures. I was hoping to use the text datatype to
allow for much larger lists, but I am unable to perform any
manipulations necessary to parse the list into a table. I have tried
PATINDEX, but it alone is not enough without the text maniuplations and
I don't think the sp_xml_preparedocument can be used in a UDF.

Anyone with any thoughts on managing large arrays in t-sql?
thanks,

Matt Weiner

[Erland Sommarskog]

[posted and mailed, please reply in news]

(ma************@med.nyu.edu) writes:

I have a bunch of SPs that all rely on a UDF that parses a comma
delimitted list of numbers into a table. Everything was working fine,
but now my application is growing and I'm starting to approach the 8000
character limit of the varChar variable used to store the list.

I would like to change the UDF only and avoid having to dig through all
of my stored procedures. I was hoping to use the text datatype to
allow for much larger lists, but I am unable to perform any
manipulations necessary to parse the list into a table. I have tried
PATINDEX, but it alone is not enough without the text maniuplations and
I don't think the sp_xml_preparedocument can be used in a UDF.

Anyone with any thoughts on managing large arrays in t-sql?
thanks,

Dan gave you an pointer to my article, but since it's quite long here is a
direct link to an example on how to this:
http://www.sommarskog.se/arrays-in-s...st-of-integers
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp