SQL Server Query superiors

SQL Server does not have a hierarchical search method that is as convenient as in Oracle. I tried to use a stored procedure to implement this function. The stored procedure was previously changed by someone else who wrote something similar.

 

Create proc getparenttype (@ type_id INT)
As
Begin
Declare @ tmp1 table ([ID] int, type_name varchar (100), parent_type_id INT)
Declare @ tmp2 table ([ID] int, type_name varchar (100), parent_type_id INT)
Declare @ tmp3 table ([ID] int, type_name varchar (100), parent_type_id INT)
Insert @ tmp1 select * From td_type where [type_id] = @ type_id
Insert @ tmp2 select * From td_type where [type_id] = @ type_id

While (select count (*) from @ tmp2)> 0
Begin
Insert @ tmp1 select * From td_type where type_id in (select [parent_type_id] From @ tmp2)
Insert @ tmp3 select * From @ tmp2
Delete @ tmp2
Insert @ tmp2 select * From td_type where type_id in (select [parent_type_id] From @ tmp3)
Delete @ tmp3
Insert @ tmp1 select * From td_type where type_id in (select [parent_type_id] From @ tmp2)
End
End
Select distinct (ID), type_name, parent_type_id from @ tmp1
Go

------------------ Run
Exec getparenttype 10015