如果块事务 sql，则无法回滚

Question

当我执行这个存储过程时，即使IF语句是正确的，我也无法在块IF回滚。如果IF语句是正确的，则脚本最终仍在运行，并且根本没有回滚。

消息：帐户 ID 已使用成功注册

Msg 3902，级别 16，状态 1，过程 PROC_DANGKY，第 23
行 COMMIT TRANSACTION 请求没有相应的 BEGIN TRANSACTION。

法典：

CREATE PROCEDURE PROC_REGISTER
    @name nvarchar(30),
    @birth datetime,
    @passport nvarchar(9),
    @address nvarchar(50),
    @phone nvarchar(11),
    @email nvarchar(20),
    @account nvarchar(30),
    @password nvarchar(20)
AS
BEGIN TRAN  
    BEGIN TRY
        IF (EXISTS(SELECT * FROM CUSTOMER WHERE ACCOUNT = @account))    
        BEGIN               
            PRINT N'The account id has already been used'
            ROLLBACK TRAN               
        END     
        INSERT INTO KHACHHANG 
        VALUES (@name, @birth, @passport, @address, @phone, @email, @account, @password)
        PRINT N'Successfully registered'
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg VARCHAR(2000)
        SELECT @ErrorMsg = N'Error: ' + ERROR_MESSAGE()
        RAISERROR(@ErrorMsg, 16,1)
        ROLLBACK TRAN
        RETURN
    END CATCH   
COMMIT TRAN

Answer 1

重新设置查询格式

存在后删除了回滚

CREATE PROC PROC_REGISTER
    @name nvarchar(30),
    @birth datetime,
    @passport nvarchar(9),
    @address nvarchar(50),
    @phone nvarchar(11),
    @email nvarchar(20),
    @account nvarchar(30),
    @password nvarchar(20)
AS
 BEGIN TRAN      
    BEGIN TRY
        IF (EXISTS(SELECT * FROM CUSTOMER WHERE ACCOUNT = @account))    
             PRINT N'The account id has already used'
        ELSE   
         BEGIN
             INSERT INTO KHACHHANG VALUES (@name, @birth, @passport,           @address, @phone, @email, @account, @password)
           PRINT N'Successul register'
         END
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg VARCHAR(2000)

          SELECT @ErrorMsg = N'Error: ' + ERROR_MESSAGE()
            RAISERROR(@ErrorMsg, 16,1)
             IF @@TRANCOUNT > 0  
                  ROLLBACK TRAN
        END CATCH   
IF @@TRANCOUNT > 0  
    COMMIT TRAN
GO  
RETURN

Answer 2

如果事务存在，您可以在回滚到 ckeck 之前检查@@TRANCOUNT：

CREATE PROC PROC_REGISTER
    @name nvarchar(30),
    @birth datetime,
    @passport nvarchar(9),
    @address nvarchar(50),
    @phone nvarchar(11),
    @email nvarchar(20),
    @account nvarchar(30),
    @password nvarchar(20)
AS
BEGIN TRAN  
    BEGIN TRY
        IF (EXISTS(SELECT * FROM CUSTOMER WHERE ACCOUNT = @account))    
            BEGIN               
                PRINT N'The account id has already used'
                IF (@@TRANCOUNT > 0)
                BEGIN
                    ROLLBACK TRAN
                END
            END     
        INSERT INTO KHACHHANG VALUES (@name, @birth, @passport, @address, @phone, @email, @account, @password)
        PRINT N'Successul register'
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg VARCHAR(2000)
        SELECT @ErrorMsg = N'Error: ' + ERROR_MESSAGE()
        RAISERROR(@ErrorMsg, 16,1)
        IF (@@TRANCOUNT > 0)
        BEGIN
            ROLLBACK TRAN
        END
        RETURN
    END CATCH   
COMMIT TRAN