• sql server try catch tran


     1 -- move approver tasks to PushTaskLog
     2 
     3 DECLARE @approverID NVARCHAR(100) = ''
     4 DECLARE @remark NVARCHAR(100) = CONVERT(NVARCHAR(19), GETDATE(), 120)
     5 DECLARE @applicationID NVARCHAR(100) = ''
     6 DECLARE @extensionID NVARCHAR(100) = ''
     7 
     8 BEGIN TRY
     9     IF OBJECT_ID('tempdb..#tmpTask') IS NOT NULL
    10         DROP TABLE #tmpTask
    11 
    12     SELECT *
    13     INTO #tmpTask
    14     FROM dbo.Task
    15     WHERE ApproverID = @approverID
    16           AND ApplicationID = @applicationID
    17           AND ExtensionID = @extensionID
    18 
    19     BEGIN TRAN
    20     INSERT dbo.PushTaskLog
    21     (
    22         ID,
    23         ApplicationID,
    24         ExtensionID,
    25         TaskID,
    26         Approver,
    27         Requestor,
    28         Title,
    29         SubmittedDate,
    30         ApproverLevel,
    31         XmlBody,
    32         CreatedDate,
    33         TaskStatus,
    34         LogStatus
    35     )
    36     SELECT NEWID(),
    37            ApplicationID,
    38            ExtensionID,
    39            TaskID,
    40            ApproverID,
    41            ApplicantName,
    42            Title,
    43            SubmittedDate,
    44            ApproverLevel,
    45            NativeApplicationData,
    46            CreatedDate,
    47            @remark,
    48            -1
    49     FROM #tmpTask
    50 
    51     DELETE main
    52     FROM dbo.Task main
    53         INNER JOIN #tmpTask tmp
    54             ON tmp.ID = main.ID
    55     COMMIT TRAN
    56 
    57     DROP TABLE #tmpTask
    58 END TRY
    59 BEGIN CATCH
    60     PRINT ERROR_MESSAGE()
    61     IF @@TRANCOUNT > 0
    62     BEGIN
    63         ROLLBACK TRAN
    64     END
    65 
    66 END CATCH
  • 相关阅读:
    Zookeeper入门
    Redis五种数据类型
    shardingJDBC分库分表
    RabbitMQ入门
    Spring-Boot
    spring-mvc
    oracle一些对表的操作命令
    对IFeatureClass的选择结果进行再次选择及排序
    关于基础 DBMS 错误 ORA-01654的总结
    Linq 如何实现 in 与 not in
  • 原文地址:https://www.cnblogs.com/lizhanglong/p/9051003.html
Copyright © 2020-2023  润新知