部门表递归查询

 
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-----------------------------------------------------------------------
 --
-- 摘 要： 当前代理商所有ID关联信息(返回：ID,PID,Name,HasChild)
-----------------------------------------------------------------------

ALTER PROCEDURE [dbo].[Sys_BaseAllInstitution]
(
@InStitutionID INT
)
AS
---- 关闭记数器 ----
SET NOCOUNT ON
; with I(ID,PID,NAME,HasChild) as(
SELECT AA.[InstitutionID] AS ID,AA.[ParentInstitutionID] AS PID ,AA.[InstitutionName] AS NAME,AA.[HasChild] FROM [Sys_InstitutionTB] AA WHERE AA.[InstitutionID]=@InStitutionID
UNION ALL
SELECT A.[InstitutionID] AS ID,A.[ParentInstitutionID] AS PID,A.[InstitutionName] AS NAME,A.[HasChild] FROM [Sys_InstitutionTB] A WHERE A.[ParentInstitutionID]=@InStitutionID
UNION ALL
SELECT B.[InstitutionID] AS ID,B.[ParentInstitutionID] AS PID,B.[InstitutionName] AS NAME,B.[HasChild] FROM I,[Sys_InstitutionTB] B
WHERE B.[ParentInstitutionID]=I.[ID]
)

SELECT ID,PID,Name,HasChild FROM [I]


---- 恢复记数器 ----
SET NOCOUNT OFF

---- 返回 ----
RETURN