SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- https://dba.stackexchange.com/q/273695/68127
create table foo (
    id int not null primary key,
    parent_id int not null references foo (id),
    is_root as convert(bit,iif(id=parent_id,1,0)) persisted not null
);
insert into foo 
(id, parent_id)
values
(1,1),
(2,1),
(3,1),
(4,3),
(5,5),
(6,5),
(7,3);

/*
 1
 |\
 2 3
  / \
 7   4

 5
  \
   6
*/
7 rows affected

CREATE PROCEDURE [dbo].[GetChain]
    @starting_id int
AS
BEGIN
-- https://dba.stackexchange.com/q/273695/68127
    WITH cte AS
    (
        SELECT 
            id, 
            parent_id,
            depth = 0,
            '/'+convert(varchar(max),id) as _path
        FROM foo
        WHERE is_root = 1
        UNION ALL
        SELECT 
            foo.id, 
            foo.parent_id,
            depth + 1,
            _path + '/'+convert(varchar(max),foo.id)
        FROM cte
        JOIN foo ON foo.parent_id = cte.id
        where cte.depth < 100 -- i can never remember the maxrecursion hint syntax
            and foo.is_root = 0
    )
    SELECT * 
    FROM cte 
    where exists ( -- children (and self)
        select 1
        from string_split(_path,'/') ss
        where ss.value = @starting_id
    ) or cte.id in ( -- parents (and self)
        select ss.[value]
        from cte b
        cross apply string_split(b._path,'/') ss
        where b.id = @starting_id
    )
    order by _path
END
-- https://dba.stackexchange.com/q/273695/68127
exec getChain 3;
/*
we expect the following nodes

 1
  \
   3
  / \
 7   4
 
2 is excluded because it is a sibling of 3, 
but not an ancestor or descendant
*/


id
parent_id
depth
_path


1
1
0
/1

3
1
1
/1/3

4
3
2
/1/3/4

7
3
2
/1/3/7