Link to home
Start Free TrialLog in
Avatar of rwheeler23
rwheeler23Flag for United States of America

asked on

Proper positioning of begin transaction and commit transaction

Is the positioning of the begin try and begin transaction and end try and commit transaction correct? I ask because I sometimes get a message about transaction count being wrong with previous count being 0 and current count being 1.

CREATE PROCEDURE [dbo].[rbs_RevalidateQuote]
      @I_vQuote_Number INT,
      @I_vSequence_Number INT,
      @I_vQuote_Date DATETIME
AS
BEGIN
      
      SET NOCOUNT ON

DECLARE @TranCount int
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int

SET @TranCount = @@TRANCOUNT

IF @TranCount = 0
      BEGIN TRAN UpdateRevalidateNumber
ELSE
      SAVE TRAN UpdateRevalidateNumber

DECLARE @REVALIDATE_NUMBER INT

-- Get the current highest revalidate number
SELECT  @REVALIDATE_NUMBER = CASE WHEN MAX(REVALIDATE_NUMB_OEQH) + 1 < 9 THEN max(REVALIDATE_NUMB_OEQH) + 1 ELSE 9 END FROM CSTQUTHD WHERE QUOTE_NUMBER_OEQH=@I_vQuote_Number AND
      SEQUENCE_NUMBER_OEQH=@I_vSequence_Number

BEGIN TRY
      
              -- Update quote header
            UPDATE CSTQUTHD
            SET REVALIDATE_NUMB_OEQH = @REVALIDATE_NUMBER,DATE_OF_QUOTE_OEQH=@I_vQuote_Date,INACTIVE_OEQH=0,CST_QT_PRT_DATE_OEQH='19010101' WHERE QUOTE_NUMBER_OEQH=@I_vQUOTE_NUMBER AND
                  SEQUENCE_NUMBER_OEQH=@I_vSequence_Number

            --      Update quote lines
            UPDATE CSTQUTLN
            SET REVALIDATE_NUMB_OEQL = @REVALIDATE_NUMBER,CUST_QT_DATE_OEQL=@I_vQuote_Date WHERE QUOTE_NUMBER_OEQL=@I_vQUOTE_NUMBER AND SEQUENCE_NUMBER_OEQL=@I_vSequence_Number

            -- Update quote kit lines
            UPDATE CSTQUTKT
            SET REVALIDATE_NUMB_OEQK = @REVALIDATE_NUMBER,CUST_QT_DATE_OEQK=@I_vQuote_Date WHERE QUOTE_NUMBER_OEQK=@I_vQUOTE_NUMBER AND SEQUENCE_NUMBER_OEQK=@I_vSequence_Number

            -- Update quote note lines
            UPDATE NOTELINE
            SET REVALIDATE_NUMB_OQNL = @REVALIDATE_NUMBER WHERE QUOTE_NUMBER_OQNL=@I_vQUOTE_NUMBER AND SEQUENCE_NUMBER_OQNL=@I_vSequence_Number

END TRY
BEGIN CATCH
            ROLLBACK TRAN UpdateRevalidateNumber
            SELECT      @ErrMsg = ERROR_MESSAGE(),
                        @ErrSeverity = ERROR_SEVERITY()
            RAISERROR(@ErrMsg, @ErrSeverity, 1)

END CATCH

-- Complete the transaction
COMMIT TRAN UpdateRevalidateNumber
END

GO
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of rwheeler23

ASKER

Perfect. Thank you.