SQL如何用事务

SQL怎么用事务


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&lt; = @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

成功返回1、失败返回-1
[最优解释]
你这个不关事务事吧。用@@rowcount来判断是否有影响行数咯，因为不成功，影响行数就为0，如果大于0，无论多少，都代表成功。然后你做判断嘛
[其他解释]
定义一个变量，中途传入@@rowcount值，做判断呗
[其他解释]

begin try
begin tran
--这里是你的语句
commit 
end try
begin catch
rollback 
end catch

[其他解释]

我想确保这个中途不出错

SQL如何用事务

热点推荐