SQL存储过程更新权限-优快云博客

本文介绍了一个SQL存储过程，用于更新用户的新闻管理权限，包括删除报告权限和添加用户权限，并通过事务确保数据的一致性和安全性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

IF EXISTS(SELECT * FROM sysobjects WHERE name='proc_news_update')
DROP PROCEDURE proc_news_update
GO
CREATE PROCEDURE proc_news_update
                 @IsAdmin varchar(10)='0',
     @DeleteReport varchar(10)='0',
                 @AddUser varchar(10)='0',
     @user_name varchar(30)
AS
        BEGIN TRAN
   DECLARE @err INT
   SET @err=0
   IF(@IsAdmin=null)
    BEGIN
     SET @IsAdmin='0'
    END
   IF(@DeleteReport=null)
    BEGIN
     SET @DeleteReport='0'
    END
   IF(@DeleteReport=null)
    BEGIN
     SET @AddUser='0'
    END
   UPDATE news_user_table SET deleteReport=CONVERT(INT,@DeleteReport),addUser=CONVERT(INT,@AddUser) WHERE userName=@user_name
   SET @err=@@ERROR
   IF(@err<>0)
    BEGIN
     ROLLBACK TRAN
    END
   ELSE
    BEGIN
     DECLARE @A INT
     SET @A=(SELECT userID FROM news_admin_table AS a INNER JOIN news_uer_table AS b ON a.userID=b.userID WHERE userName=@user_name)
     UPDATE news_admin_table SET IsAdmin=CONVERT(INT,@DeleteReport) WHERE userID=@A
     SET @err=@@ERROR
     IF(@err<>0)
      BEGIN
       ROLLBACK TRAN
      END
     ELSE
      BEGIN
       COMMIT TRAN
      END
    END
GO

一段sql代码