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

總結

以上是生活随笔為你收集整理的mssql查询所有上下级的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。