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) + '" οnclick=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)+'" οnclick=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