合作
咨询
帮助
建站咨询 
- 作者：xiaoxiao
- 发表时间：2020-12-23 10:38
- 来源：未知
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 GOSET ANSI_NULLS ON GO
CREATE procedure sp_GenDeptTable ( @cols int, @tds varchar(50) , @trs varchar(50) , @tables varchar(50) )asdeclare @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.deptidopen cfetch from c into @pid, @pname, @did, @dnameselect @v = @tables + @trsselect @n = ''while @@fetch_status = 0begin 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)endclose cdeallocate cselect v as '选择发文范围' from @rGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
