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)