SQL statement Recursion Algorithm

The table structure is as follows:

Department superior department
A B
B c
C d
A
B
C

Find an SQL statement and check its superior department according to A. the query result is
Superior department
B
C
D

========================================================== ==========

Functions
Create Table Tb (Department varchar (20), higher-level department varchar (20 ))

Insert into TB select 'A', 'B' Union all select 'B', 'C' Union all select 'C', 'D'
Union all select 'A', 'A' Union all select 'B', 'B' Union all select 'C', 'C'

-- Select * from TB
Create Function test_f (@ name varchar (20 ))
Returns @ ta table (superior department varchar (20 ))
As
Begin
-- Select @ name = superior department from TB where Department = @ name and department! = Superior department
While exists (select 1 from TB where Department = @ name and department! = Higher-level department)
Begin
Insert @ ta select superior department from TB where Department = @ name and department! = Superior department
Select @ name = superior department from TB where Department = @ name and department! = Superior department
End
Return
End

Select * From DBO. test_f ('A ')

Delete:
Drop function test_f
Drop table TB

Superior department
--------------------
B
C
D

(The number of affected rows is 3)