November 18, 2011 at 8:54 am
I want to know what is wrong with the stored procedure. It works fine with all cases except when I try to raiserror , I get the following error:-
Substitute Product has already been filled
Msg 266, Level 16, State 2, Procedure spUpdateStockRequestDetail, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Here is the stored procedure :-
ALTER PROCEDURE [dbo].[spUpdateStockRequestDetail]
@srID bigint,
@srDID bigint,
@qtyOrdered decimal,
@qtyApproved decimal,
@subITIProdCode nvarchar(100),
@qtySubstitute decimal,
@qtyCancelled decimal
AS
DECLARE @subLinkID bigint
DECLARE @prevStatus int
BEGIN
BEGIN TRY
BEGIN TRANSACTION
--Firstly get the id of substitute Product if there is any
SELECT @subLinkID = subLinkID from ITIStockRequestDetail WHERE srdID =@srDID
-- Secondly check if the order needs to be cancelled
IF (@qtyOrdered =@qtyCancelled )
BEGIN
-- Change the status of Parent Record
UPDATE ITIStockRequestDetail SET qtyApproved =@qtyApproved ,qtySubstitute=@qtySubstitute ,subLinkID=0,qtyCancelled =@qtyCancelled, status =4
WHERE srdID =@srDID
-- Delete the child record if any
IF (@subLinkID > 0)
DELETE FROM ITIStockRequestDetail WHERE srdID =@subLinkID
END
ELSE
-- Go through all different scenarios to update the record
BEGIN
IF (@subLinkID = 0 )
BEGIN
--If qtysubstitute is greater than 0 ,create Substitution with Status =1 Open
IF (@qtySubstitute > 0)
BEGIN
INSERT INTO ITIStockRequestDetail (srID,ITIprodCode,subLinkID,qtyOrdered,qtyApproved,qtySubstitute,qtyCancelled,status)
VALUES(@srID ,@subITIProdCode ,0,0.00,@qtySubstitute,0.00 ,0.00,1)
SET @subLinkID = SCOPE_IDENTITY()
---Update the Parent Record with NEw substitute and Status Open
UPDATE ITIStockRequestDetail SET qtyApproved =@qtyApproved,qtySubstitute=@qtySubstitute,subLinkID=@subLinkID,qtyCancelled =@qtyCancelled, status =1
WHERE srdID =@srDID
END
ELSE
-- Just Update the Parent Record with Status Open
BEGIN
UPDATE ITIStockRequestDetail SET qtyApproved =@qtyApproved,qtySubstitute=@qtySubstitute,subLinkID=0,qtyCancelled =@qtyCancelled, status =1
WHERE srdID =@srDID
END
END
ELSE
BEGIN
SELECT @prevQtySubstitute =qtyApproved, @prevStatus=status
FROM ITIStockRequestDetail WHERE srdID =@subLinkID
IF (@prevStatus = 2) --filled
BEGIN
IF (@prevQtySubstitute <> @qtySubstitute )
BEGIN
--DO NOTHING once the substitute is filled you cannot change it
RAISERROR('Substitute Product has already been filled',10,1)
RETURN
END
ELSE
BEGIN
-- Parent Product with new quantity approved and qtycancelled
UPDATE ITIStockRequestDetail SET qtyApproved =@qtyApproved,qtyCancelled =@qtyCancelled, status =1
WHERE srdID =@srDID
END
END
ELSE
BEGIN
IF (@qtySubstitute =0)
BEGIN
-- Delete the substitute Record and Update the Parent Record
DELETE FROM ITIStockRequestDetail WHERE srdID =@subLinkID
---Update
UPDATE ITIStockRequestDetail SET qtyApproved =@qtyApproved,qtySubstitute=0.00,subLinkID=0,qtyCancelled =@qtyCancelled, status =1
WHERE srdID =@srDID
END
ELSE
BEGIN
--Update the subtitute Product
UPDATE ITIStockRequestDetail SET qtyApproved =@qtySubstitute , ITIprodCode =@subITIProdCode , status =1
WHERE srdID =@subLinkID
-- Parent Product with new quantities
UPDATE ITIStockRequestDetail SET qtyApproved =@qtyApproved,qtySubstitute=@qtySubstitute,qtyCancelled =@qtyCancelled, status =1
WHERE srdID =@srDID
END
END
END
END
COMMIT TRAN
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
BEGIN
ROLLBACK TRAN
END
END CATCH
END
November 18, 2011 at 12:37 pm
change your severity to 11
The probability of survival is inversely proportional to the angle of arrival.
November 19, 2011 at 5:33 am
I am giving here one example to help you in understanding the issue.
PRINT @@TRANCOUNT
-- The BEGIN TRAN statement will increment the
-- transaction count by 1.
BEGIN TRAN
PRINT @@TRANCOUNT
BEGIN TRAN
PRINT @@TRANCOUNT
-- The ROLLBACK statement will clear the @@TRANCOUNT variable
-- to 0 because all active transactions will be rolled back.
ROLLBACK
PRINT @@TRANCOUNT
--Results
--0
--1
--2
--0
Use @@TRANCOUNT intelligently in your script to overcome your issue.
November 19, 2011 at 8:03 pm
Try inserting ROLLBACK between your RAISERROR and your immediately following RETURN.
Or, as sturner suggests, increase the sverity to one that makes the error catchable (errors with severity 10 or below are not catchable).
Tom
November 21, 2011 at 12:23 am
The problem is the RETURN immediately after the RAISERROR.
The 1st answer from @sturner of increasing the severity should work as this will force execution of the CATCH.
As an aside, I would rather see validation performed before you start the transaction. Less scope for this kind of issue, as well as fewer transactions being started.
November 21, 2011 at 6:58 am
Thanks Everyone , It was really helpful.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply