mssql查询所有上下级

if exists (select * from sys.all_objects where name='GetOrgTreeByID')
begin
drop proc GetOrgTreeByID
end
go

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[GetOrgTreeByID]

@ID int,--查询的ID
@QueryType nvarchar(50) --查询方式,down:查询所有下级,up:查询所有上级

AS
BEGIN
IF(@QueryType='down')
begin
with DownLevel as
(
select id,ParentID,OrgName, 0 as lvl from tabOrg
where id = @ID
union all
select d.id,d.ParentID,d.Orgname,lvl + 1 from DownLevel c inner join tabOrg d
on c.Id = d.ParentID
)
select * from DownLevel
end
else
begin
with UpLevel as
(
select id,ParentID,OrgName, 0 as lvl from tabOrg
where id = @ID
union all
select d.id,d.ParentID,d.Orgname,lvl + 1 from UpLevel c inner join tabOrg d
on c.ParentID = d.id
)
select * from UpLevel
end
END
GO
--exec GetOrgTreeByID 2,'up'

转载于:https://www.cnblogs.com/SilenceTom/p/5576050.html