这是存储过程中写好的事务吗 [英] Is this good writen transaction in stored procedure

问题描述

这是我第一次使用事务，我只是想知道我做对了吗.我应该改变什么吗?我插入帖子(一缕).插入帖子时，我需要在 commentableEntity 表中生成 ID 并将该 ID 插入到 wisp 表中.

This is the first time that I use transactions and I just wonder am I make this right. Should I change something? I insert post(wisp). When insert post I need to generate ID in commentableEntity table and insert that ID in wisp table.

ALTER PROCEDURE [dbo].[sp_CreateWisp]
@m_UserId uniqueidentifier,
@m_WispTypeId int,
@m_CreatedOnDate datetime,
@m_PrivacyTypeId int,
@m_WispText nvarchar(200)
AS
BEGIN TRANSACTION

    DECLARE @wispId int

    INSERT INTO dbo.tbl_Wisps
    (UserId,WispTypeId,CreatedOnDate,PrivacyTypeId,WispText)
    VALUES
    (@m_UserId,@m_WispTypeId,@m_CreatedOnDate,@m_PrivacyTypeId,@m_WispText)

    if @@ERROR <> 0
        BEGIN
            ROLLBACK            
            RAISERROR ('Error in adding new wisp.', 16, 1)
            RETURN
        END

    SELECT @wispId = SCOPE_IDENTITY()

    INSERT INTO dbo.tbl_CommentableEntity
    (ItemId)
    VALUES
    (@wispId)

    if @@ERROR <> 0
        BEGIN
            ROLLBACK            
            RAISERROR ('Error in adding commentable entity.', 16, 1)
            RETURN
        END

    DECLARE @ceid int

    select @ceid = SCOPE_IDENTITY()

    UPDATE dbo.tbl_Wisps SET CommentableEntityId = @ceid WHERE WispId = @wispId

    if @@ERROR <> 0
        BEGIN
            ROLLBACK            
            RAISERROR ('Error in adding wisp commentable entity id.', 16, 1)
            RETURN
        END

COMMIT

使用基于@gbn 的 try/catch 答案:

Using try/catch based on @gbn answer:

ALTER PROCEDURE [dbo].[sp_CreateWisp]
@m_UserId uniqueidentifier,
@m_WispTypeId int,
@m_CreatedOnDate datetime,
@m_PrivacyTypeId int,
@m_WispText nvarchar(200)
AS

SET XACT_ABORT, NOCOUNT ON

DECLARE @starttrancount int

BEGIN TRY
    SELECT @starttrancount = @@TRANCOUNT

    IF @starttrancount = 0
        BEGIN TRANSACTION

        DECLARE @wispId int

        INSERT INTO dbo.tbl_Wisps
        (UserId,WispTypeId,CreatedOnDate,PrivacyTypeId,WispText)
        VALUES
        (@m_UserId,@m_WispTypeId,@m_CreatedOnDate,@m_PrivacyTypeId,@m_WispText)

        SELECT @wispId = SCOPE_IDENTITY()

        INSERT INTO dbo.tbl_CommentableEntity
        (ItemId)
        VALUES
        (@wispId)

        DECLARE @ceid int

        select @ceid = SCOPE_IDENTITY()

        UPDATE dbo.tbl_Wisps SET CommentableEntityId = @ceid WHERE WispId = @wispId

    IF @starttrancount = 0 
        COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 AND @starttrancount = 0 
        ROLLBACK TRANSACTION
    RAISERROR ('Error in adding new wisp', 16, 1)
END CATCH
GO

推荐答案

你会从 SQL Server 2005+ 开始使用 TRY/CATCH

You'd use TRY/CATCH since SQL Server 2005+

您的回滚进入 CATCH 块，但您的代码在其他方面看起来不错(使用 SCOPE_IDENTITY() 等).我也会使用 SET XACT_ABORT, NOCOUNT ON

Your rollback goes into the CATCH block but your code looks good otherwise (using SCOPE_IDENTITY() etc). I'd also use SET XACT_ABORT, NOCOUNT ON

这是我的模板:嵌套存储过程包含试试 CATCH ROLLBACK 模式?

• 这允许按照 DeveloperX 的回答进行嵌套事务
• 根据 Randy 的评论，此模板还允许进行更高级别的交易

这篇关于这是存储过程中写好的事务吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！