27,579
社区成员
发帖
与我相关
我的任务
分享
ALTER proc [dbo].[sp_roleAdd]
(
@roleId varchar(20),
@roleName varchar(20),
@permissionIds nvarchar(max)
)
as
begin
declare @permissionId varchar(20)
declare @powerButtonId varchar(20)
declare @menuIdOrButtonId varchar(20)
declare @buttonId varchar(20)
begin try
begin tran
insert into qx_roleInfo(roleId,roleName) values(@roleId,@roleName)
declare roleCursor Cursor for
select Value from dbo.SplitString(@permissionIds,',',1)
open roleCursor;
fetch next from roleCursor into @permissionId
while @@fetch_status=0
begin
declare roleCursor1 Cursor for
select Value from dbo.SplitString(@permissionId,'-',1)
open roleCUrsor1
declare @loopCount int
declare @roleMenuId bigint
set @loopCount=0
fetch next from roleCUrsor1 into @menuIdOrButtonId
while @@FETCH_STATUS=0
begin
if(@loopCount=0)
begin
insert into qx_roleMenu(roleId,menuId) values(@roleId,@menuIdOrButtonId)
set @roleMenuId=@@IDENTITY;
end
else
begin
insert into qx_roleMenuButton(roleMenuId,buttonId) values(@roleMenuId,@menuIdOrButtonId)
end
set @loopCount=@loopCount+1;
end
close roleCursor1
deallocate roleCursor1
fetch next from roleCursor into @permissionId
end
close roleCursor
DEALLOCATE roleCursor
commit tran
end try
begin catch
rollback tran
--此处如何关闭roleCursor1??
end catch
return 1
end
ALTER proc [dbo].[sp_roleAdd]
(
@roleId varchar(20),
@roleName varchar(20),
@permissionIds nvarchar(max)
)
as
begin
declare @permissionId varchar(20)
declare @powerButtonId varchar(20)
declare @menuIdOrButtonId varchar(20)
declare @buttonId varchar(20)
begin try
begin tran
insert into qx_roleInfo(roleId,roleName) values(@roleId,@roleName)
declare roleCursor Cursor for
select Value from dbo.SplitString(@permissionIds,',',1)
open roleCursor;
fetch next from roleCursor into @permissionId
while @@fetch_status=0
begin
declare roleCursor1 Cursor LOCAL FOR
select Value from dbo.SplitString(@permissionId,'-',1)
open roleCUrsor1
declare @loopCount int
declare @roleMenuId bigint
set @loopCount=0
fetch next from roleCUrsor1 into @menuIdOrButtonId
while @@FETCH_STATUS=0
begin
if(@loopCount=0)
begin
insert into qx_roleMenu(roleId,menuId) values(@roleId,@menuIdOrButtonId)
set @roleMenuId=@@IDENTITY;
end
else
begin
insert into qx_roleMenuButton(roleMenuId,buttonId) values(@roleMenuId,@menuIdOrButtonId)
end
set @loopCount=@loopCount+1;
end
close roleCursor1
deallocate roleCursor1
fetch next from roleCursor into @permissionId
end
close roleCursor
DEALLOCATE roleCursor
commit tran
end try
begin catch
rollback tran
IF CURSOR_STATUS('LOCAL','roleCursor1')=1
BEGIN
close roleCursor1
DEALLOCATE roleCursor1
END
end catch
return 1
end