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