sql server - TSQL: Handle infinite loop in data -

i have table folderxdoc:

create table [dbo].[folderxdoc]( [fldid] [int] not null, [xorder] [int] not null, [docid] [int] not null, constraint [folderxdoc$pk] primary key clustered  ( [fldid] asc, [xorder] asc, [docid] asc )with (pad_index  = off, statistics_norecompute  = off, ignore_dup_key = off, allow_row_locks  = on, allow_page_locks  = on) on [primary] ) on [primary] 

my application allows cyclic references in table, following data allowed:

fldid|xorder|docid 1|1|2 2|1|3 3|1|4 4|1|2 

so folder 1 contains folder 2 contains folder 3. folder 3 contains folder 4. folder 4 contains folder 2 have cycle (1/2/3/4/2/3/4/2/3/4/...)

now want retrieve contained elements of folder recursively. tried cte, because of cycle of data not work. stop recursion when loop detected. when retrieve contained elements of 1 i'd expect result set (2,3,4).

i tried user defined function:

create function [dbo].[docchildren](@fldid int) returns table return (      n             (select f.fldid, f.docid             folderxdoc f f.fldid = @fldid            union             select n.fldid, nplus1.docid               folderxdoc nplus1, n                 n.docid = nplus1.fldid , n.docid != @fldid)       select docid n   ) 

the function handles cyclic loop of starting id, not when cycle occurs in contained element. can solve problem?

thanks help!

perhaps temp table mark when node has been visited may help.

basically visit each node, push temp table , check each node against temp table. stop when find existing node.

might need cursors implement though, bad may be.