CODEWITHSUNDEEP
sqlsql-serverstored-procedurestransactions
Dũng Kon
Dũng Kon

Reputation: 69

Cannot rollback in if block transaction sql

When I executed this stored procedure, I cannot rollback in IF block even the IF statement was right. If the IF statement was right, script still running in the end and there is no rollback at all.

The message: The account id has already used successful register

Msg 3902, Level 16, State 1, Procedure PROC_DANGKY, Line 23
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Code:

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

Upvotes: 1

Views: 1541

Answers (2)

Backs
Backs

Reputation: 24913

You can check @@TRANCOUNT before rollback to ckeck if transaction exists:

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

Upvotes: 0

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

REFORMATTED your query


Removed rollback after exists

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

Upvotes: 1

Related Questions