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

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

zhaodaxiang

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

阅读量643

点赞数

本文链接：https://blog.csdn.net/zhaodaxiang/article/details/5753777

版权

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