Th. Fuchs (10/14/2013)
---- drop the recursion into a static tableif object_id('dbo._numbers') is null --- drop table dbo._numbers
begin
print 'create static collection of numbers'
create table dbo._numbers (n integer primary key(n))
declare @i integer = 0
set nocount on
while @i <= 214748 --3647 -- the hidden recursion
begin insert into dbo._numbers(n) values(@i) select @i += 1 end
end
select @start = getdate()
select [substring] = substring(@p,
case when n = 0 then 0 else n + 1 end, -- start of token
isnull(nullif(charindex(',', @p, n + 1), 0), len(@p)) - n +
case when n = 0 then 0 -- start at position 0
when charindex(',', @p, n + 1) = 0 then 0 -- the last token
else -1 end -- len of token - len(delimiter)
)
from dbo._numbers where n = 0 or substring(@p, n, 1) = ','
---- or hide recursion in the stack (attention, max 31 item pssible)
create function dbo.stt(@source varchar(8000)) returns @t table (t varchar(8000)) as
begin
declare @token varchar(8000), @l integer
select @l = @@nestlevel
if @source like '%,%'
begin -- token exists
select @token = left(@source, charindex(',', @source) -1) -- cut first token
select @source = right(@source, len(@source) - len(@token) -1) -- trimm tail
insert into @t(t) values(@token) -- the one token found into resultset
insert into @t(t) select t from dbo.stt(@source) -- the same procedure for the rest THE RECURSION
end -- first token cutted
else
begin -- last token found
insert into @t(t) values(@source) -- the last feather
end -- now ready
return
end
If you read the title of this post, the OP wanted to be able to do this without a loop or CTE. The first script you have above uses a loop. The second script avoids an explicit loop but has the same problem as a loop insofar as being RBAR, not to mention the recursive call limit you identified.
--Jeff Moden
Change is inevitable... Change for the better is not.