取其某个ID为父节点的树结构的SQL

CREATE PROCEDURE [dbo].[GetAllChildOrgByOrgParent]
-- Add the parameters for the stored procedure here
@id int
AS
BEGIN
--create table Example(ID int,ParentID int)
--取其某个ID为父节点的树结构的SQL如下：
WITH tmp_Table(id,ParentID,parentName)
AS
(
--取根节点放入临时表
SELECT OrgId,OrgParent,OrgName FROM BasOrg WHERE OrgId = @ID
--根据已取到的数据递归取其节点的数据
UNION ALL
SELECT OrgId,orgParent,OrgName FROM BasOrg A INNER JOIN tmp_Table B ON A.OrgParent = B.id
)
SELECT * FROM tmp_Table
END