Whats wrong with it, Transaction mismatch.. Please help.

  • IF OBJECT_ID('test_parent_table') IS NOT NULL

    DROP TABLE test_parent_table

    CREATE TABLE test_parent_table

    (idINT,

    nameVARCHAR(30)

    )

    GO

    IF OBJECT_ID('DataError') IS NOT NULL

    DROP TABLE DataError

    CREATE TABLE DataError

    (ErrorNoINT,

    ErrorDescVARCHAR(1000),

    ErrorProcVARCHAR(100),

    ErrorLineNumberINT,

    ErrorDateTimeDATETIME2(7)

    )

    GO

    IF OBJECT_ID('ThrowError') IS NOT NULL

    DROP PROCEDURE ThrowError

    GO

    CREATE PROCEDURE ThrowError

    AS

    BEGIN

    DECLARE@errorNoINT,

    @errorDescVARCHAR(1000),

    @errorProcVARCHAR(100),

    @errorLineNumberINT,

    @errorDateTimeDATETIME2(7)

    SELECT@errorNo=ERROR_NUMBER(),

    @errorDesc=ERROR_MESSAGE(),

    @errorProc=ERROR_PROCEDURE(),

    @errorLineNumber=ERROR_LINE(),

    @errorDateTime=GETDATE()

    DECLARE @id INT = XACT_STATE()

    IF( @errorNo < 50000)

    BEGIN

    IF XACT_STATE() > 0 ROLLBACK

    INSERT INTO DataError

    SELECT@errorNo,

    @errorDesc,

    @errorProc,

    @errorLineNumber,

    @errorDateTime

    --RAISERROR(@errorDesc,16,1);

    --RETURN 1

    END

    ELSE

    BEGIN

    RAISERROR(@errorDesc,16,1);

    END

    END;

    GO

    IF OBJECT_ID('test_child') IS NOT NULL

    DROP PROCEDURE test_child

    GO

    CREATE PROCEDURE test_child

    (@idINT,

    @nameVARCHAR(50)

    )

    AS

    BEGIN

    BEGIN TRY

    INSERT INTO test_parent_table

    SELECT@id,

    @name

    SELECT1/0

    RETURN 0

    END TRY

    BEGIN CATCH

    EXEC ThrowError

    RETURN 1

    END CATCH

    END

    GO

    IF OBJECT_ID('test_parent') IS NOT NULL

    DROP PROCEDURE test_parent

    GO

    CREATE PROCEDURE test_parent

    (@idINT,

    @nameVARCHAR(50)

    )

    AS

    BEGIN

    BEGIN TRY

    BEGIN TRAN

    INSERT INTO test_parent_table

    VALUES( 1,'Harry')

    EXEC test_child 2,'Kristena'

    SELECT 1/0

    INSERT INTO test_parent_table

    VALUES( 3,'Chris')

    IF( @@TRANCOUNT > 0)

    COMMIT

    RETURN 0

    END TRY

    BEGIN CATCH

    EXEC ThrowError

    END CATCH

    END

    GO

    /* Test cases

    EXEC test_parent 0,'test'

    SELECT * FROM test_parent_table

    SELECT * FROM DataError

    */

    WHEn I run the test case above , the output in DataError is given below:-

    ErrorDesc

    1 Row :- Divide by zero error encountered.

    2 Row :- Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

    Here second row is not supposed to come, this is sucking. Please help!

    Regards

    Ashok

  • AND ANSWER IS

    IF OBJECT_ID('test_parent_table') IS NOT NULL

    DROP TABLE test_parent_table

    CREATE TABLE test_parent_table

    (idINT,

    nameVARCHAR(30)

    )

    GO

    IF OBJECT_ID('DataError') IS NOT NULL

    DROP TABLE DataError

    CREATE TABLE DataError

    (ErrorNoINT,

    ErrorDescVARCHAR(1000),

    ErrorProcVARCHAR(100),

    ErrorLineNumberINT,

    ErrorDateTimeDATETIME2(7)

    )

    GO

    IF OBJECT_ID('ThrowError') IS NOT NULL

    DROP PROCEDURE ThrowError

    GO

    CREATE PROCEDURE ThrowError

    AS

    BEGIN

    DECLARE@errorNoINT,

    @errorDescVARCHAR(1000),

    @errorProcVARCHAR(100),

    @errorLineNumberINT,

    @errorDateTimeDATETIME2(7)

    SELECT@errorNo=ERROR_NUMBER(),

    @errorDesc=ERROR_MESSAGE(),

    @errorProc=ERROR_PROCEDURE(),

    @errorLineNumber=ERROR_LINE(),

    @errorDateTime=GETDATE()

    DECLARE @id INT = XACT_STATE()

    IF( @errorNo < 50000)

    BEGIN

    IF XACT_STATE() <> 0

    BEGIN

    ROLLBACK

    DECLARE @id34 INT = XACT_STATE()

    END

    INSERT INTO DataError

    SELECT@errorNo,

    @errorDesc,

    @errorProc,

    @errorLineNumber,

    @errorDateTime

    RAISERROR(@errorDesc,16,1);

    --RETURN 1

    END

    ELSE

    BEGIN

    RAISERROR(@errorDesc,16,1);

    END

    END;

    GO

    IF OBJECT_ID('test_child') IS NOT NULL

    DROP PROCEDURE test_child

    GO

    CREATE PROCEDURE test_child

    (@idINT,

    @nameVARCHAR(50)

    )

    AS

    BEGIN

    BEGIN TRY

    BEGIN TRAN

    INSERT INTO test_parent_table

    SELECT@id,

    @name

    SELECT1/0

    DECLARE @id11 INT = @@TRANCOUNT

    IF( @@TRANCOUNT > 0)

    COMMIT

    RETURN 0

    END TRY

    BEGIN CATCH

    DECLARE @id1 INT = XACT_STATE()

    DECLARE @id12 INT = @@TRANCOUNT

    IF XACT_STATE() <> 0 ROLLBACK

    EXEC ThrowError

    END CATCH

    END

    GO

    IF OBJECT_ID('test_parent') IS NOT NULL

    DROP PROCEDURE test_parent

    GO

    /* Test cases

    EXEC test_parent 0,'test'

    SELECT * FROM test_parent_table

    SELECT * FROM DataError

    */

    CREATE PROCEDURE test_parent

    (@idINT,

    @nameVARCHAR(50)

    )

    AS

    BEGIN

    BEGIN TRY

    BEGIN TRAN

    INSERT INTO test_parent_table

    VALUES( 1,'Harry')

    EXEC test_child 2,'Kristena'

    SELECT 1/0

    INSERT INTO test_parent_table

    VALUES( 3,'Chris')

    DECLARE @id11 INT = @@TRANCOUNT

    IF( @@TRANCOUNT > 0)

    COMMIT

    RETURN 0

    END TRY

    BEGIN CATCH

    DECLARE @id1 INT = XACT_STATE()

    DECLARE @id12 INT = @@TRANCOUNT

    IF XACT_STATE() <> 0 ROLLBACK

    EXEC ThrowError

    END CATCH

    END

    GO

    /* Test cases

    EXEC test_parent 0,'test'

    SELECT * FROM test_parent_table

    SELECT * FROM DataError

    */

    It is now perfect : I am sorry to bother you guys..:-P:hehe:

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply