[原创]SQL Server 存储过程获取全部父级ID

USE [ParkAdv]
GO
/****** 对象:  StoredProcedure [dbo].[proc_UpperDep]    脚本日期: 11/22/2014 17:17:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[proc_UpperDep](@Dep_id int,@ShowThisDep bit)
as
declare @UpperId int,@depidlist varchar(100),@sql varchar(max)
set @depidlist=''

if(@ShowThisDep=1)
begin
	set @depidlist=@depidlist+convert(varchar(5),@Dep_id)+','
end

select @UpperId=UpperId from Departments where Dep_id=@Dep_id

while(@UpperId>0)
begin
	select @Dep_id=Dep_id from Departments where Dep_id=@UpperId
	select @UpperId=UpperId from Departments where Dep_id=@Dep_id
	set @depidlist=@depidlist+convert(varchar(5),@Dep_id)+','
end

select @depidlist = left(@depidlist,len(@depidlist)-1)

set @sql = 'select * from Departments where Dep_id in ('+@depidlist+') order by Dep_id desc'
exec(@sql)