create or alter procedure CIT_TABLE
(
  DIRID type of column TREATPLACE.DIRID,
  DCODE type of column CLREFERRALS.DCODE,
  TREATCODE type of column CLREFERRALS.TREATCODE
)
returns
(
  SCHID type of column CLREFDET.SCHID,
  SCHEMANAME blob sub_type text,
  SPOSOB type of column TOOTHNAM.TOOTHNAME,
  NOMBERSTEKLO type of column DICINFO.REKVINT3
)
as
begin
if (coalesce(DIRID,0)=0) then
begin
for
select crd.schid schid, w.kodoper||' '||w.schname schemaname, tn.toothname sposob , di.rekvint3 nombersteklo
									from clreferrals cr
									left join clrefdet crd on cr.refid=crd.refid
									join dicinfo di on crd.schid=di.rekvint1 and di.refid=-700 and di.rekvint3<=crd.scount and di.rekvint2=crd.organid
									left join toothnam tn on crd.organid=tn.toothcod
									left join wschema w on w.schid=crd.schid
									where cr.treatcode=:treatcode
						      order by schemaname, sposob, nombersteklo
                  into :SCHID, :SCHEMANAME, :SPOSOB, :NOMBERSTEKLO
do
suspend;
end
end

DECLARE @source_table TABLE (ID INT, ID_product INT, COUNT INT)
 
INSERT INTO @source_table (ID, ID_product, COUNT)
    VALUES (1, 12457, 7),
            (2, 11111, 3),
            (3, 22222, 4),
            (4, 33333, 1)
            
SELECT * FROM @source_table
 
;WITH cteRepeat (ID, ID_product, COUNT, lvl)
AS
(
SELECT ID, ID_product, COUNT, 1 AS lvl FROM @source_table
UNION ALL
SELECT A.ID, A.ID_product, A.count, lvl + 1
FROM @source_table AS A
    INNER JOIN cteRepeat AS B
    ON A.ID = B.ID AND A.count> B.lvl
)
SELECT * FROM cteRepeat ORDER BY ID, lvl

select crd.schid schid, w.kodoper||' '||w.schname schemaname, tn.toothname sposob, crd.scount
from clreferrals cr
left join clrefdet crd on cr.refid=crd.refid
left join toothnam tn on crd.organid=tn.toothcod
left join wschema w on w.schid=crd.schid
where cr.treatcode=:treatcode

create or alter procedure TEST2
(
TREATCODE type of column CLREFERRALS.TREATCODE
)
returns(
SCHID type of column CLREFDET.SCHID,
SCHEMANAME blob sub_type text,
SPOSOB type of column TOOTHNAM.TOOTHNAME,
SCOUNT type of column CLREFDET.SCOUNT,
LVL INTEGER
)
as
begin
for
WITH recursive Repeat(SCHID, SCHEMANAME, SPOSOB, SCOUNT, LVL)
AS
(
select crd.schid schid, w.kodoper||' '||w.schname schemaname, tn.toothname sposob, crd.scount, 1 as lvl
from clreferrals cr
left join clrefdet crd on cr.refid=crd.refid
left join toothnam tn on crd.organid=tn.toothcod
left join wschema w on w.schid=crd.schid
where cr.treatcode=:treatcode
UNION all
select crd1.schid schid, w1.kodoper||' '||w1.schname schemaname, tn1.toothname sposob, crd1.scount, lvl+1
from clreferrals cr1
left join clrefdet crd1 on cr1.refid=crd1.refid
left join toothnam tn1 on crd1.organid=tn1.toothcod
left join wschema w1 on w1.schid=crd1.schid
JOIN Repeat AS CRT on crd1.schid=crt.schid and crd1.scount>crt.lvl
where cr1.treatcode=:treatcode
)
select SCHID, SCHEMANAME, SPOSOB, SCOUNT, LVL from Repeat
into SCHID, SCHEMANAME, SPOSOB, SCOUNT, LVL
do
suspend;
end

create or alter procedure TEST2
(
TREATCODE type of column CLREFERRALS.TREATCODE
)
returns(
SCHID type of column CLREFDET.SCHID,
SCHEMANAME blob sub_type text,
SPOSOB type of column TOOTHNAM.TOOTHNAME,
SCOUNT type of column CLREFDET.SCOUNT,
LVL INTEGER
)
as
begin
for
WITH recursive Repeat(SCHID, SCHEMANAME, SPOSOB, SCOUNT, LVL)
AS
(
select crd.schid schid, w.kodoper||' '||w.schname schemaname, tn.toothname sposob, crd.scount, 1 as lvl
from clreferrals cr
left join clrefdet crd on cr.refid=crd.refid
left join toothnam tn on crd.organid=tn.toothcod
left join wschema w on w.schid=crd.schid
where cr.treatcode=:treatcode
UNION all
select crd1.schid schid, w1.kodoper||' '||w1.schname schemaname, tn1.toothname sposob, crd1.scount, lvl+1
from clreferrals cr1
left join clrefdet crd1 on cr1.refid=crd1.refid
left join toothnam tn1 on crd1.organid=tn1.toothcod
left join wschema w1 on w1.schid=crd1.schid
JOIN Repeat AS RPT on crd1.schid=rpt.schid and crd1.scount>rpt.lvl and tn1.toothname=rpt.sposob
where cr1.treatcode=:treatcode
)
select SCHID, SCHEMANAME, SPOSOB, SCOUNT, LVL from Repeat
into SCHID, SCHEMANAME, SPOSOB, SCOUNT, LVL
do
suspend;
end