sql server 存储过程事务处理实现批量删除

roleidstr使用逗号隔开的字符串如“001.，002，003，004，005”

create procedure DeleteRoles
(
    @roleidstr  nvarchar(500)   
)
AS
BEGIN
set nocount on
set xact_abort on
begin tran
    DECLARE @role_id     varchar(10)
    DECLARE    @i          INT
    DECLARE    @len        INT   
   
    IF (@roleidstr IS NULL) OR (LTRIM(@roleidstr) = '')
        RETURN
   
    WHILE CHARINDEX(',',@roleidstr) > 0
        BEGIN
            SET @len = LEN(@roleidstr)
            SET @i = CHARINDEX(',', @roleidstr)
            SET @role_id = LEFT(@roleidstr, @i-1)
            Delete from  t_RoleAuthority where RoleID=@role_id--少做修改，改成需要的sql语句即可
          if(@@error<>0) begin
  rollback tran
  return -1 end
   else delete from t_Role where RoleID=@role_id
  if(@@error<>0) begin
  rollback tran
  return -1 end 
        SET @roleidstr = RIGHT(@roleidstr, @len - @i)
        END
    commit tran
   END
GO

 

 

 

 

 

 

create proc roleDelete(@roleid varchar(10))
as
begin
set nocount on
set xact_abort on
begin tran
delete from t_RoleAuthority where RoleID=@roleid
if(@@error<>0) begin
rollback tran
return -1 end
else delete from t_Role where RoleID=@roleid
if(@@error<>0) begin
rollback tran
return -1 end
commit tran
end