日期:2014-05-17 浏览次数:20545 次
declare @RoleIDs table(RoleID int,RowNum int)
insert into @RoleIDs
select RoleID,row_number() over (order by RoleID desc) as RowNum from T_Role
declare @RoleCount int
select @RoleCount=count(RoleID) from T_Role
declare @i int
set @i=1
while @i< = @RoleCount --SQL不支持for循环,用while
begin
declare @NowRoleID int
select @NowRoleID=RoleID from @RoleIDs where RowNum=@i --遍历得到的当前RoleID
declare @ModuleIDs table(ModuleID int)
declare @RID int
set @RID=@NowRoleID --赋值
insert into @ModuleIDs
select ModuleID from T_Role_Module where RoleID=@RID
declare @Data table(RoleID int,PermisnID int,ModuleID int) insert into @Data
select @RID as RoleID,PermisnID,ModuleID from T_Module_Permisn where ModuleID in (select * from @ModuleIDs)
set @i=@i+1
end
delete from T_Role_Permisn
insert into T_Role_Permisn(RoleID,PermisnID,ModuleID) select * from @Data
begin try
begin tran
--这里是你的语句
commit
end try
begin catch
rollback
end catch