CODEWITHSUNDEEP
sqlsql-servert-sqlrecursive-querysql-function
Sanyam Madaan
Sanyam Madaan

Reputation: 139

CTE goes in infinite loop?

I have a table structure like this

enter image description here

Where I want to recur till if(text_id = new_text_id). So suppose I pass 1 as text_id I want in return 5 as text_id using CTE.

I tried but it goes in infinite loop, tried maxrecursion still 

WITH textHierarchy AS (
    SELECT tm.text_id
    FROM text_master tm 
    WHERE tm.text_id = 1

    UNION ALL

    SELECT tm.text_id
    FROM text_master as tm
    JOIN textHierarchy AS txtHr ON tm.text_id = txtHr.new_text_id -- Terminating Condition
    WHERE txtHr.new_text_id IS NOT NULL 

)
SELECT * FROM textHierarchy option (maxrecursion 5);

Please tell me what I am doing wrong.

My aim is to use this CTE inside a database function and call that function from Java.

And can a CTE be used inside a function. If so how?

Upvotes: 0

Views: 336

Answers (3)

lptr
lptr

Reputation: 6808

create table dbo.text_master_test
(
text_id int,
text_details nvarchar(max),
new_text_id int
)
go

insert into text_master_test
values(1, 'det 1',2), (2, 'det 2',3), (3, 'det 3',4), (4, 'det 4',5), (5, 'det 5',5);
go


WITH textHierarchy AS (
    SELECT tm.text_id, tm.new_text_id, nullif(tm.new_text_id, tm.text_id) as next_text_id
    FROM text_master_test tm 
    WHERE tm.text_id = 1
    UNION ALL
    SELECT tm.text_id, tm.new_text_id, nullif(tm.new_text_id, tm.text_id) as next_text_id
    FROM text_master_test as tm
    JOIN textHierarchy AS txtHr ON tm.text_id = txtHr.next_text_id 

)
SELECT * FROM textHierarchy;
go


create function dbo.textrecursion(@start_text_id int)
returns table
as
return
(
WITH textHierarchy 
AS 
(
    SELECT tm.text_id, tm.text_details, tm.new_text_id, 
        nullif(tm.new_text_id, tm.text_id) as next_text_id
    FROM dbo.text_master_test tm 
    WHERE tm.text_id = @start_text_id
    UNION ALL
    SELECT tm.text_id, tm.text_details, tm.new_text_id,
        nullif(tm.new_text_id, tm.text_id) as next_text_id
    FROM dbo.text_master_test as tm
    JOIN textHierarchy AS txtHr ON tm.text_id = txtHr.next_text_id 
)
select text_id, text_details, new_text_id
from textHierarchy
);
go

select *
from dbo.textrecursion(1)

select *
from dbo.textrecursion(4)

select *
from dbo.textrecursion(5)
go

drop function dbo.textrecursion;
go
drop table dbo.text_master_test
go

Upvotes: 1

B.Muthamizhselvi
B.Muthamizhselvi

Reputation: 642

Can you try this below query. I passed text_id=1

;WITH Numbers(text_id) AS
(
    SELECT text_id
    from rectab
    where text_id=1
    UNION ALL
    SELECT text_id
    FROM rectab
    WHERE text_id=new_text_id
)
SELECT text_id
FROM Numbers;

Upvotes: 0

GMB
GMB

Reputation: 222672

What you commented as a termination condition is not that. That's a joining condition that defines the relation between the current iteration and the next one.

I think that you want:

WITH textHierarchy AS (
    SELECT tm.text_id
    FROM text_master tm 
    WHERE tm.text_id = 1

    UNION ALL

    SELECT tm.text_id
    FROM text_master as tm
    JOIN textHierarchy AS txtHr ON tm.text_id = txtHr.new_text_id 
    WHERE txtHr.text_id <> txtHr.new_text_id -- Termination condition

)
SELECT * FROM textHierarchy option (maxrecursion 5);

Upvotes: 0

Related Questions