[dba-SQLServer]Close but no cigar

You need a BEGIN --END GROUP for your WHILE loop. If you don't put one, then 
SS only includes the next statement as part of your WHILE loop, in which 
case produced an infinite loop.

BTW, select * from fn_StringToTable( '100, 200, 333, 444, 555') only 
produces:

100
200
333
444

HTH
Billy


>From: "Arthur Fuller" <artful at rogers.com>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: "dba-SQLServer" <dba-SQLServer at databaseadvisors.com>
>Subject: [dba-SQLServer]Close but no cigar
>Date: Mon, 1 Sep 2003 10:59:40 -0700
>
>I'm trying to write a function that parses a string and returns a table of
>its values. So far I'm close but need some help on the final step.
>
>This preliminary codes in QA:
>
>declare @string varchar(50)
>set @string = '100, 200, 300, 400'
>
>declare @pos int
>while @string > ''
>	begin
>	set @pos = charindex(',', @string)
>	if @pos > 0
>		begin
>		select substring( @string, 1, @pos - 1)
>		select @string = LTrim(substring( @string, @pos + 1, 
>LEN(@string)- at pos+1))
>		select @string
>		end
>	else
>		set @string = ''
>	end
>
>This works fine and runs pretty quickly.
>
>Next, I tried to turn it into a table-valued function, like so:
>
>CREATE FUNCTION fn_StringToTable (@String varchar(50))
>RETURNS @Values TABLE (ID int primary key)
>AS
>begin
>--	declare @string varchar(50)
>--	set @string = '100, 200, 300, 400'
>
>	declare @pos int
>	declare @value int
>
>	while @string > ''
>	-- begin
>	set @pos = charindex(',', @string)
>	if @pos > 0
>		begin
>		set @value = substring( @string, 1, @pos - 1)
>		select @string = LTrim(substring( @string, @pos + 1, 
>LEN(@string)- at pos+1))
>		--select @string
>		insert @Values SELECT @value
>		end
>	else
>		set @string = ''
>
>	RETURN
>end
>
>When I run this version, i.e. "select * from fn_StringToTable( '100, 200,
>333, 444, 555')", it seems to go into space. Doesn't crash or anything, but
>I keep cancelling because it's taking so long.
>
>What am I doing wrong?
>
>TIA,
>Arthur
>
>---
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.510 / Virus Database: 307 - Release Date: 8/14/2003
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>

_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*  
http://join.msn.com/?page=features/junkmail