[dba-SQLServer]Close but no cigar

Thanks, Billy!

Here's my revised code:

<sql>
ALTER FUNCTION fn_StringToTable (@String varchar(100))
RETURNS @Values TABLE (ID int primary key)
AS
begin

	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
		IF len(@string) > 0
		   begin
		   set @value = @string
		   insert @Values SELECT @value
  		   set @string = ''
		   end
	else
  		   set @string = ''
	end

	-- just to see what I'm doing
	--SELECT * FROM @Values
	RETURN
end

</sql>

It now works as it should, finding the '555' at the end, but the tests look
a little clumsy to me. Can you think of any optimizations?

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Billy
Pang
Sent: Monday, September 01, 2003 9:27 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [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

---
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