I pass a comma-delimitted string of numbers to a sproc that can be
huge. I'm using this Split function to break up the list of numbers by
comma. It works great but only holds up to the varchar limit of 8000
and my requirements often exceed that. I think SQL Server 2005 uses a
new MAX property for varchars but I'm still using SQL Server 2000. Is
this any way to overcome this? Thanks.


CREATE PROCEDURE [dbo].[up_ExportQuickSearchresults]
@p_selectedDirectors VARCHAR(8000)
AS
BEGIN

SELECT * FROM v_QuickSearchResults
WHERE IDDir in (SELECT IDDir FROM split(@p_selectedDirectors, ','))
END GO



CREATE FUNCTION dbo.Split
(
@ItemList VARCHAR(8000),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (IDDir VARCHAR(8000))
AS

BEGIN
DECLARE @tempItemList VARCHAR(8000)
SET @tempItemList = @ItemList

DECLARE @i INT
DECLARE @IDDir VARCHAR(8000)

SET @tempItemList = REPLACE (@tempItemList, ' ', '')
SET @i = CHARINDEX(@delimiter, @tempItemList)

WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @IDDir = @tempItemList
ELSE
SET @IDDir = LEFT(@tempItemList, @i - 1)
INSERT INTO @IDTable(IDDir) VALUES(@IDDir)
IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList,
LEN(@tempItemList) - @i)
SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END

A few ways to handle this:

1). Pass multiple VARCHAR parameters to the stored procedure
2). Pass TEXT parameter to the stored procedure and internally splice the
TEXT to VARCHAR chunks and process
3). Pass TEXT parameter that contains XML formatted values rather than list,
then use OPENXML to process

Take a look at this article by Erland Sommarskog for details on some of the
techniques, as well as different methods to split a list to optimize that
part too:
http://www.sommarskog.se/arrays-in-sql-2000.html

In particular those two sections:
http://www.sommarskog.se/arrays-in-s...lnum-unlimited
http://www.sommarskog.se/arrays-in-s...0.html#OPENXML

HTH,

Plamen Ratchev
http://www.SQLStudio.com