Using Lists

[ice7899]

I'm cycling through a list using the code below. Is there any way to improve this code or is this the most efficient way?

DECLARE @list varchar(30);
DECLARE @element varchar(30);
DECLARE @d char(1);
DECLARE @x INT;
SET @d = ',';
SET @list = 'hello,world,out,there';
SET @list = @list + @d;
SET @x = charindex(@d,@list);
print @list;
WHILE (@x > 0) BEGIN
SET @element = left(@list,@x-1);
PRINT @element;
SET @list = substring(@list,@x+1,len(@list));
SET @x = charindex(@d,@list);
END

 

[gmmastros]

Any sort of loops in sql server are inherently slow. I've seen several variations of this same topic. With relatively short lists, this is acceptable. However, if your list is long, there are problems.

Longer lists have a bigger performance problem. Additionally, with SQL 2000, a varchar variable is limited to 8000 characters. In my opinion, if this list grows longer than about a hundred or so items, then you are probably better off using XML to do this. This thread: thread220-1249932 has an example of how this can be done with XML.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom