存储过程

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GenDeptTable]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GenDeptTable]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE procedure sp_GenDeptTable (
 @cols int,
 @tds varchar(50) ,
 @trs varchar(50) ,
 @tables varchar(50)
)
as
declare @r table (
 v varchar(8000)
)
declare @did bigint,
  @pid bigint,
  @dname varchar(1000),
  @pname varchar(1000),
  @i int, @n varchar(1000),
  @v varchar(8000)
declare c cursor for
 select d0.deptid, d0.name, d1.deptid,
  '<input type="checkbox" name="d_' + convert(varchar(1000), d0.deptid) + '_' + convert(varchar(1000), d1.deptid) + '" onclick=checkvalue(this) value = "' +d1.name + '" id="' +convert(varchar(1000), d1.deptid) + '">' + d1.name + '</input>'
 from department d0
  inner join department d1 on d0.deptid = d1.parentdeptid and d0.parentdeptid is null
 order by d0.deptid, d1.deptid
open c
fetch from c into @pid, @pname, @did, @dname
select @v = @tables + @trs
select @n = ''
while @@fetch_status = 0
begin
 if @n <> @pname
 begin
  select @n = @pname
  insert into @r values ('<input type="checkbox" name="g_'+convert(varchar(1000), @pid)+'" onclick=checkvalue(this) value = "' +@pname+ '" id="' + convert(varchar(1000), @pid) + '">' + @pname + '</input>')
 end
 select @v = @tables + @trs + @tds + @dname + '</td>'
 select @i = 0

 fetch next from c into @pid, @pname, @did, @dname
 while @@fetch_status = 0 and @i < @cols-1 and @pname = @n
 begin
  select @i = @i + 1
  select @v = @v + @tds + @dname + '</td>'
  fetch next from c into @pid, @pname, @did, @dname
 end

 while @i < @cols - 1
 begin
  select @v = @v + @tds + '</td>'
  select @i = @i + 1
 end
 select @v = @v + '</tr></table>'
 insert into @r values (@v)
end
close c
deallocate c
select v as '选择发文范围' from @r
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO