Recursive code help

Transact-SQL
21

I tested this this morning and it worked.

create view a
as 
with cte as
 (   SELECT
        [S].[Item]
        , [S].[Part_Num]
        , [S].[Replaced_Part_Num]
        , [S].[Part_Noun]
    FROM
        [Sample] AS [S]
    WHERE
        [S].[Replaced_Part_Num] = 'PN102'
	union all
	  SELECT
        [S].[Item]
        , [S].[Part_Num]
        , [S].[Replaced_Part_Num]
        , [S].[Part_Noun]
    FROM
        [Sample] AS [S]
			join cte c
				on s.Replaced_Part_Num = c.Part_Num		)

	select * from cte
22

Mike, I confirm the code runs fine. My fat fingers took away one of the "on" before "join" messed me up. But, now I have some issues with the data. Say, I have, instead of 202 points to 203, it actually self reference. The table is designed that way for look up the replaceable part because the parts indeed changaeable both ways

select 8 ,'PN102','PN202', 'PartName302' union -- **new...
Expectedly: The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
is there a way to tell it to stop the recursion when it begins to loop indefinitely?

Create table Sample (
Item int,
Part_Num varchar(6),
Replaced_Part_Num varchar(5),
Part_Noun varchar(30)
)
Insert into Sample
select 1 ,'PN501','PN401', 'PartName501' union
select 2 ,'PN502','PN402', 'PartName502' union
select 3 ,'PN503','PN403', 'PartName503' union
select 4 ,'PN401','PN301', 'PartName401' union
select 5 ,'PN3402','PN302', 'PartName3403' union
select 6 ,'PN403','PN303', 'PartName403' union
select 7 ,'PN2301','PN201', 'PartName2301' union
select 8 ,'PN102','PN202', 'PartName302' union --New
select 9 ,'PN303','PN203', 'PartName303' union
select 10 ,'PN201','PN101', 'PartName201' union
select 11 ,'PN202','PN102', 'PartName202' union
select 12 ,'PN203','PN103', 'PartName203' union
select 10 ,'PN101','NULL', 'PartName101' union
select 11 ,'PN102','NULL', 'PartName102' union
select 12 ,'PN103','NULL', 'PartName103'
Query 1:

 ;with cte as
 (   SELECT
        [S].[Item]
        , [S].[Part_Num]
        , [S].[Replaced_Part_Num]
        , [S].[Part_Noun]
    FROM
        [Sample] AS [S]
    WHERE
        [S].[Part_Num] = 'PN102'
	union all
	  SELECT
        [S].[Item]
        , [S].[Part_Num]
        , [S].[Replaced_Part_Num]
        , [S].[Part_Noun]
    FROM
        [Sample] AS [S]
			join cte c
				on s.Replaced_Part_Num = c.Part_Num)

	select * from cte
23

...looks like I can put a max recursion = 2 or something to handle the condition.

24

You can add the MaxRecusion Option, but be careful as it could run in infinite loop, if using 0

Select * from VIEWNAME option (MaxRecursion 0)

25

Tried 2, 10 and 20 for maxrecursion. It is not a permanent solution though, is it? I can't get the result set for one, even I set it to 20. Fiddle kills it at 100 anyway. I can't seem to use it within a query expression. When observing the data, typically, a self-referring Part_Nbr will appear in the following recursion. e.g. 202 => 302, next iteration, 302 => 202.

26

so how is it supposed to handle that type of scenario?

27

Currently, it errors out. No output, regardless whether I sent the maxrecursion to 10 or 20.

Desired output: will be to simply include the first replace_part_num, ignore the resursive part and move on to PN103, and finish the CTE, and tabulated the first resursive result with the rest.

Saw an implementation with a count and count+1 to see the level of recursive (example employee and manager and sr. manager, etc.