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