pro_manager_userrole.sql

来自「一套宾馆管理系统」· SQL 代码 · 共 17 行

create procedure managerRole
@mode int ,
@username varchar(20),
@roleid  int
as
if @mode=1 --add
   begin 
    if not exists(select * from userroles a left outer join userlist b on a.userid=b.userid   where b.username=@username and a.roleid=@roleid )
    insert into userroles(userid,roleid) select userid ,@roleid from userlist where username=@username
   end 
if @mode=2 --remove 
   begin
    if  exists(select * from userroles a left outer join userlist b on a.userid=b.userid   where b.username=@username and a.roleid=@roleid )
    delete from userroles where roleid=@roleid and userid in(select userid from userlist where username=@username)
   end