Try...catch With Transactions

I have 3 procedures where the first one calls the second one and the second one calls the third one. All procedures have try..catch blocks and transactions. In the third procedure I declare an INT variable and try to give it the value 'A', to create an error. This makes the code in the catch block to run. In the catch block I rollback the transaction and return 1. It then returns to the 2nd procedure, but here I get the exception "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 2, current count = 0. Is there I way to keep running the code in the second procedure without getting this error? I take care of the return value from the 3rd procedure which is 1 so I know that there was an error in the third procedure but I want to run the code below the procedure call to the 3rd procedure in the second procedure, is there I way to do this?

I tried to have a transaction only in the first procedure but then I get this error "The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction."

What shoule I do to continue running the code in procedure 2 and procedure 1 after the procedure calls? In real life I have another scenario but the code below is just an easy example that creates the same error that my real procedures create.

Procedure 1:





Code Snippet
ALTER PROCEDURE [dbo].[ProcFirst]
AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY

DECLARE @return int
BEGIN TRANSACTION




EXEC @return = [dbo].[ProcSecond]
IF @return <> 0
BEGIN

SELECT 'return value <> 0 i ProcFirst'
RETURN 1
END
IF (XACT_STATE()) = 1 COMMIT TRANSACTION
RETURN 0

END TRY
BEGIN CATCH -- Error-handling statements


-- End Transaction
IF @@TRANCOUNT > 0 BEGIN

IF (XACT_STATE()) = -1 ROLLBACK TRANSACTION;
IF (XACT_STATE()) = 1 COMMIT TRANSACTION;
END


SELECT 'catch ProcFirst', @ErrorMessage


RETURN 1

END CATCH
END






Procedure 2:




Code Snippet
ALTER PROCEDURE [dbo].[ProcSecond]
AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY

DECLARE @return int
BEGIN TRANSACTION




EXEC @return = [dbo].[ProcThird]




IF @return <> 0
BEGIN

-- End Transaction
IF @@TRANCOUNT > 0
BEGIN

IF (XACT_STATE()) = -1
BEGIN

SELECT 'RollBack transaction'
ROLLBACK TRANSACTION;
END
IF (XACT_STATE()) = 1
BEGIN

Select 'commit transaction'
COMMIT TRANSACTION;
END
END
SELECT 'return value <> 0 i ProcSecond'
RETURN 1
END



IF (XACT_STATE()) = 1 COMMIT TRANSACTION
RETURN 0
END TRY
BEGIN CATCH -- Error-handling statements

-- End Transaction
IF @@TRANCOUNT > 0
BEGIN

IF (XACT_STATE()) = -1 ROLLBACK TRANSACTION;
IF (XACT_STATE()) = 1 COMMIT TRANSACTION;
END



SELECT 'catch ProcSecond', @ErrorMessage



RETURN 1
END CATCH
END






3rd procedure:




Code Snippet
ALTER PROCEDURE [dbo].[ProcThird]
AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY

DECLARE @tal int
BEGIN TRANSACTION




SET @Tal = 'a'
IF (XACT_STATE()) = 1 COMMIT TRANSACTION;
RETURN 0
END TRY
BEGIN CATCH -- Error-handling statements
--End Transaction

IF @@TRANCOUNT > 0
BEGIN

IF (XACT_STATE()) = -1
BEGIN

SELECT 'RollBack transaction ProcThird'
ROLLBACK TRANSACTION;
END
IF (XACT_STATE()) = 1
BEGIN

Select 'commit transaction ProcThird'
COMMIT TRANSACTION;
END
END



RETURN 1
END CATCH
END






Please help me!

//Emelia