SQL获取员工所有上级部门

 

USE [T120211230133657806]
GO
/****** Object:  StoredProcedure [dbo].[pro_G_Department]    Script Date: 2022/3/30 16:12:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<LT>
-- Create date: <20220325>
-- Description:	<获取员工所有上级部门>
-- =============================================
ALTER PROCEDURE  [dbo].[pro_G_Department]
	-- Add the parameters for the stored procedure here
	@UserID uniqueidentifier
AS
BEGIN
	declare @DeptId nvarchar(100)
	SELECT @DeptId=DeptId FROM Tecul_Employee WHERE  Id=@UserID;
	WITH TEMP AS
	(
		SELECT * FROM  tecul_Department  WHERE Id=@DeptId  
		UNION ALL
		SELECT T0.* FROM TEMP ,tecul_Department T0 WHERE TEMP.ParentId=T0.Id 
	)
	SELECT * FROM Tecul_Department  where  Id  in (SELECT Id FROM TEMP) ORDER BY Level;
END