SqlServer 存储过程的事务

最新推荐文章于 2024-06-13 16:58:20 发布

守着寂寞守着你

最新推荐文章于 2024-06-13 16:58:20 发布

阅读量1.4k

点赞数

分类专栏：数据库文章标签： sqlserver 存储 table

本文链接：https://blog.csdn.net/WeiZhang_son_Ding/article/details/4888934

版权

数据库专栏收录该内容

11 篇文章 0 订阅

订阅专栏

注意:在事务当中不能再开始事务

ALTER PROCEDURE [dbo].[MyProcedure]
       @Flag               AS      INT,
       @Column1           AS        VARCHAR(8),
       @Column2               AS       VARCHAR(50),
       @Column3       AS       VARCHAR(50),
       @Column4           AS       CHAR(10),
       @Column5           AS        TINYINT,
       @Column6       AS       TINYINT,
       @Column7       AS       NVARCHAR(20),
       @Column8           AS       CHAR(2),
       @Column9           AS       NVARCHAR(50),
        @O_RETURN           AS       INT   OUTPUT

AS
BEGIN
    SET NOCOUNT ON
   BEGIN TRAN--------------------------开始事务

       UPDATE Table1
          SET Column2 = @Column2
           , Column3 = @Column3
       WHERE Column1 = @Column1
       IF @@ERROR <> 0
           GOTO errback

   IF @Flag = 1
       UPDATE Table2
       SET Column2 = @Column2
           , Column3 = @Column3
           , Column4 =@Column4
           , Column5 =@Column5
           , Column6 =@Column6
           , Column7 = @Column7
           , Column8 = @Column8
           , Column9 = @Column9
       WHERE Column1 = @Column1
   ELSE
       UPDATE Table3
       SET Column2 = @Column2
           , Column3 = @Column3
           , Column4 =@Column4
           , Column5 =@Column5
           , Column6 =@Column6
       WHERE Column1 = @Column1

       IF @@ERROR <> 0 ---------因为if else 当中语句只执行一个,所以判断放在外面
           GOTO errback ----------转到执行错误的语句去;

       UPDATE Table4
       SET Column2 = @Column2
       WHERE Column1 = @Column1
       IF @@ERROR <> 0
           GOTO errback

           COMMIT TRAN   -- 提交事务
       SET @O_RETURN = 0;--返回成功标志
       RETURN --必须 return 掉,否则会执行下面的 errback:当中的代码
       errback: --处理错误
           ROLLBACK TRAN --回滚事务
           SET @O_RETURN= 1;
           RETURN