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

最新推荐文章于 2024-03-26 16:42:58 发布

原创最新推荐文章于 2024-03-26 16:42:58 发布 · 692 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql server #存储 #delete #null #sql

本文介绍了一种在数据库中批量删除角色及其权限的方法，通过存储过程实现，支持传入多个角色ID并逐一删除对应的角色及权限信息。

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