SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [dbo].[foo](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[parent_id] [int] NULL,
 CONSTRAINT [PK_foo] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


ALTER TABLE [dbo].[foo]  WITH CHECK ADD  CONSTRAINT [FK_foo_foo] FOREIGN KEY([parent_id])
REFERENCES [dbo].[foo] ([id])


ALTER TABLE [dbo].[foo] CHECK CONSTRAINT [FK_foo_foo]


insert into foo (parent_id) values (null)
insert into foo (parent_id) values (1)
insert into foo (parent_id) values (1)
insert into foo (parent_id) values (1)
insert into foo (parent_id) values (2)
insert into foo (parent_id) values (2)
insert into foo (parent_id) values (3)
insert into foo (parent_id) values (3)
insert into foo (parent_id) values (3)
insert into foo (parent_id) values (4)
insert into foo (parent_id) values (5)
insert into foo (parent_id) values (6)
insert into foo (parent_id) values (7)
insert into foo (parent_id) values (8)
14 rows affected

select * from foo


id
parent_id


1
null

2
1

3
1

4
1

5
2

6
2

7
3

8
3

9
3

10
4

11
5

12
6

13
7

14
8


CREATE PROCEDURE [dbo].[GetParents]
    @starting_id int
AS
BEGIN
    WITH chainIDsUpwards AS
    (
        SELECT id, parent_id FROM foo WHERE id = @starting_id

        UNION ALL

        SELECT foo.id, foo.parent_id FROM foo
        JOIN chainIDsUpwards p ON p.id = foo.parent_id
    )

    SELECT id FROM chainIDsUpwards
END
EXEC [GetParents] @starting_id = 9


id


9
id	parent_id
1	null
2	1
3	1
4	1
5	2
6	2
7	3
8	3
9	3
10	4
11	5
12	6
13	7
14	8