create proc p_select_modules @vuid int as begin create table #module( modulecode int, rightcode int ) declare @depcode int,@rolecode int,@mcodes int,@i int,@max int set @depcode =(select departmentcode from employees where userid=@vuid) set @rolecode=(select rolecode from roleusers where userid=@vuid)
if exists (select userid from userrights where userid=@vuid) insert into #module(modulecode,rightcode) select modulecode,rightcode from userrights where userid=@vuid if exists (select departmentcode from deprights where departmentcode=@depcode) insert into #module (modulecode,rightcode) select modulecode,rightcode from deprights where modulecode not in(select modulecode from #module) and departmentcode=@depcode if exists (select rightcode from rolerights where rolecode=@rolecode) insert into #module (modulecode,rightcode) select modulecode,rightcode from rolerights where modulecode not in(select modulecode from #module) and rolecode=@rolecode declare smodule cursor for select modulecode from #module open smodule fetch next from smodule into @mcodes while @@fetch_status=0 begin select e.modulecode,e.modulename,f.childmodulecode,f.childmodulename from (select a.modulecode,b.modulename from modulemanager as a left outer join moduledef b on a.modulecode=b.modulecode where a.modulecode=@mcodes ) as e left outer join (select c.modulecode,c.childmodulecode,d.modulename as childmodulename from modulemanager c left outer join moduledef d on c.childmodulecode=d.modulecode where c.modulecode =@mcodes ) as f on e.modulecode=f.modulecode fetch next from smodule into @mcodes end close smodule deallocate smodule end代码如上,连一个都没返回出来,就告诉我成功了!
alter proc p_select_modules @vuid int as begin create table #module( modulecode int, rightcode int ) --以下为填充临时表 declare @depcode int,@rolecode int,@mcodes int set @depcode =(select departmentcode from employees where userid=@vuid) set @rolecode=(select rolecode from roleusers where userid=@vuid)
if exists (select userid from userrights where userid=@vuid) insert into #module(modulecode,rightcode) select modulecode,rightcode from userrights where userid=@vuid if exists (select departmentcode from deprights where departmentcode=@depcode) insert into #module (modulecode,rightcode) select modulecode,rightcode from deprights where modulecode not in(select modulecode from #module) and departmentcode=@depcode if exists (select rightcode from rolerights where rolecode=@rolecode) insert into #module (modulecode,rightcode) select modulecode,rightcode from rolerights where modulecode not in(select modulecode from #module) and rolecode=@rolecode --临时表生成完毕 declare smodule cursor for select modulecode from #module open smodule fetch next from smodule into @mcodes while @@fetch_status=0 begin select e.modulecode,e.modulename,f.childmodulecode,f.childmodulename from (select a.modulecode,b.modulename from modulemanager as a left outer join moduledef b on a.modulecode=b.modulecode where a.modulecode=@mcodes ) as e left outer join (select c.modulecode,c.childmodulecode,d.modulename as childmodulename from modulemanager c left outer join moduledef d on c.childmodulecode=d.modulecode where c.modulecode =@mcodes ) as f on e.modulecode=f.modulecode fetch next from smodule into @mcodes end close smodule deallocate smodule end
@vuid int
as
begin
create table #module(
modulecode int,
rightcode int
) declare @depcode int,@rolecode int,@mcodes int,@i int,@max int
set @depcode =(select departmentcode from employees where userid=@vuid)
set @rolecode=(select rolecode from roleusers where userid=@vuid)
if exists (select userid from userrights where userid=@vuid)
insert into #module(modulecode,rightcode)
select modulecode,rightcode from userrights where userid=@vuid
if exists (select departmentcode from deprights where departmentcode=@depcode)
insert into #module (modulecode,rightcode)
select modulecode,rightcode from deprights
where modulecode not in(select modulecode from #module)
and departmentcode=@depcode
if exists (select rightcode from rolerights where rolecode=@rolecode)
insert into #module (modulecode,rightcode)
select modulecode,rightcode from rolerights
where modulecode not in(select modulecode from #module)
and rolecode=@rolecode declare smodule cursor for select modulecode from #module
open smodule
fetch next from smodule into @mcodes
while @@fetch_status=0
begin select e.modulecode,e.modulename,f.childmodulecode,f.childmodulename from
(select a.modulecode,b.modulename from modulemanager as a left outer join moduledef b on a.modulecode=b.modulecode where a.modulecode=@mcodes ) as e
left outer join
(select c.modulecode,c.childmodulecode,d.modulename as childmodulename from modulemanager c left outer join moduledef d on c.childmodulecode=d.modulecode where c.modulecode =@mcodes ) as f
on e.modulecode=f.modulecode
fetch next from smodule into @mcodes
end
close smodule
deallocate smodule
end代码如上,连一个都没返回出来,就告诉我成功了!
@vuid int
as
begin
create table #module(
modulecode int,
rightcode int
)
--以下为填充临时表
declare @depcode int,@rolecode int,@mcodes int
set @depcode =(select departmentcode from employees where userid=@vuid)
set @rolecode=(select rolecode from roleusers where userid=@vuid)
if exists (select userid from userrights where userid=@vuid)
insert into #module(modulecode,rightcode)
select modulecode,rightcode from userrights where userid=@vuid
if exists (select departmentcode from deprights where departmentcode=@depcode)
insert into #module (modulecode,rightcode)
select modulecode,rightcode from deprights
where modulecode not in(select modulecode from #module)
and departmentcode=@depcode
if exists (select rightcode from rolerights where rolecode=@rolecode)
insert into #module (modulecode,rightcode)
select modulecode,rightcode from rolerights
where modulecode not in(select modulecode from #module)
and rolecode=@rolecode
--临时表生成完毕 declare smodule cursor for select modulecode from #module
open smodule
fetch next from smodule into @mcodes
while @@fetch_status=0
begin select e.modulecode,e.modulename,f.childmodulecode,f.childmodulename from
(select a.modulecode,b.modulename from modulemanager as a left outer join moduledef b on a.modulecode=b.modulecode where a.modulecode=@mcodes ) as e
left outer join
(select c.modulecode,c.childmodulecode,d.modulename as childmodulename from modulemanager c left outer join moduledef d on c.childmodulecode=d.modulecode where c.modulecode =@mcodes ) as f
on e.modulecode=f.modulecode
fetch next from smodule into @mcodes
end
close smodule
deallocate smodule
end