rwheeler23
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_vQuot e_Number AND
SEQUENCE_NUMBER_OEQH=@I_vS equence_Nu mber
BEGIN TRY
-- Update quote header
UPDATE CSTQUTHD
SET REVALIDATE_NUMB_OEQH = @REVALIDATE_NUMBER,DATE_OF _QUOTE_OEQ H=@I_vQuot e_Date,INA CTIVE_OEQH =0,CST_QT_ PRT_DATE_O EQH='19010 101' WHERE QUOTE_NUMBER_OEQH=@I_vQUOT E_NUMBER AND
SEQUENCE_NUMBER_OEQH=@I_vS equence_Nu mber
-- Update quote lines
UPDATE CSTQUTLN
SET REVALIDATE_NUMB_OEQL = @REVALIDATE_NUMBER,CUST_QT _DATE_OEQL =@I_vQuote _Date WHERE QUOTE_NUMBER_OEQL=@I_vQUOT E_NUMBER AND SEQUENCE_NUMBER_OEQL=@I_vS equence_Nu mber
-- Update quote kit lines
UPDATE CSTQUTKT
SET REVALIDATE_NUMB_OEQK = @REVALIDATE_NUMBER,CUST_QT _DATE_OEQK =@I_vQuote _Date WHERE QUOTE_NUMBER_OEQK=@I_vQUOT E_NUMBER AND SEQUENCE_NUMBER_OEQK=@I_vS equence_Nu mber
-- Update quote note lines
UPDATE NOTELINE
SET REVALIDATE_NUMB_OQNL = @REVALIDATE_NUMBER WHERE QUOTE_NUMBER_OQNL=@I_vQUOT E_NUMBER AND SEQUENCE_NUMBER_OQNL=@I_vS equence_Nu mber
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
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_vQuot
SEQUENCE_NUMBER_OEQH=@I_vS
BEGIN TRY
-- Update quote header
UPDATE CSTQUTHD
SET REVALIDATE_NUMB_OEQH = @REVALIDATE_NUMBER,DATE_OF
SEQUENCE_NUMBER_OEQH=@I_vS
-- Update quote lines
UPDATE CSTQUTLN
SET REVALIDATE_NUMB_OEQL = @REVALIDATE_NUMBER,CUST_QT
-- Update quote kit lines
UPDATE CSTQUTKT
SET REVALIDATE_NUMB_OEQK = @REVALIDATE_NUMBER,CUST_QT
-- Update quote note lines
UPDATE NOTELINE
SET REVALIDATE_NUMB_OQNL = @REVALIDATE_NUMBER WHERE QUOTE_NUMBER_OQNL=@I_vQUOT
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER