The combination of SQL Server recursion and function gets the Department collection

Recursive format:

With CTE name (field list)
as
         ( 
Query Statements
          ) 

Get all subordinate department statements for the current department:

Create function getchildrendepartments (@currentdepartid int)

Returns table

As

Return

With CTE (Id,mainid)

As

(select Id,mainid from u_department where [email protected]Currentdepartid

                       UNION ALL

                       Select A.id,a.mainid from U_department a inner join CTE B on a.mainid=b.id

)

SELECT * FROM CTE

Special attention:

If you want to get all the parent department collections for the current department: The red part should be replaced with A.id=b.mainid

This article is from the "My Site" blog, please make sure to keep this source http://2160285.blog.51cto.com/2150285/1582132

SQL Server recursion and function get the Department collection