SQL statement Recursion Algorithm

The table structure is such an SQL statement for the superior department ABBCCDAABBCC. Based on A's query of its superior department, the query result is the createtabletb function (Department varchar (20) of BCD of the superior department ), superior department varchar (20) insertintotb

The table structure is such A department's upper-level department a B c d a B c. Find an SQL statement and check its upper-level department according to, the query result is superior department B C D ================================== ===================== use the create table tb function (Department varchar (20 ), parent department varchar (20) insert into tb

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)

,