April 6, 2010 at 5:18 pm
I’m having trouble passing OUTPUT parameters between Stored Procedures. Before I added the Try/Catch code, I had error handling at each SQL statement and when Proc2 tried to RETURN to Proc1 (the line of code is noted below) the error indicated that Proc2 rolled back the transaction but Proc1 expected 1 transaction. Now I just get a "severe error...". I haven't done much parameter passing between procs so I'm sure I'm just doing something wrong but I can't figure out what it is. Can anyone help me out? Thanks.
Proc1 does the following:
CREATE PROCEDURE Proc1
--declare the variables passed to the stored procedure
@Success INTEGER OUTPUT, -- 0 = FALSE, 1 = TRUE
@Message VARCHAR(50) OUTPUT
AS
BEGIN TRY
DECLARE @pSuccess INTEGER,
@pMessage VARCHAR(50)
BEGIN TRANSACTION
…
EXEC Proc2 @Param, @pSuccess OUTPUT, @pMessage OUTPUT
If @pSuccess = 0
BEGIN
SET @Success = @pSuccess
SET @Message = @pMessage
ROLLBACK TRANSACTION
RETURN
END
…
COMMIT TRANSACTION
RETURN
END TRY
BEGIN CATCH
…
END CATCH
Proc2 does the following:
CREATE PROCEDURE Proc2
--declare the variables passed to the stored procedure
@Success INTEGER OUTPUT, -- 0 = FALSE, 1 = TRUE
@Message VARCHAR(50) OUTPUT
AS
BEGIN TRY
BEGIN TRANSACTION
IF (SELECT …) < 0
BEGIN
SET @SUCCESS = 0
SET @MESSAGE = ‘INSUFFICIENT INVENTORY!’
ROLLBACK TRANSACTION
RETURN <<<<This line returns: A severe error occurred on the current command. The results, if any, should be discarded.
END
…
COMMIT TRANSACTION
RETURN
END TRY
BEGIN CATCH
…
END CATCH
April 6, 2010 at 6:15 pm
UPDATE:
I was able to correct the condition that caused the error by using the method described in this discussion: http://www.developer.com/db/article.php/10920_3768671_1/TIP-Nested-Stored-Procedure-Calls-with-SQL-Server-Transactions.htm
As I suspected, the error had to do with the rolling back of the transaction in the called proc but when I added the TRY/CATCH, the error message became very vague. Problem is solved.
May 22, 2010 at 5:32 am
Can you please elaborate how u solved?
i am also facing the same problem.. the return statement is causing the error message... :crying:
Kindest Regards,
Paarthasarathy
Microsoft Certified Technology Specialist
http://paarthasarathyk.blogspot.com
May 24, 2010 at 11:04 am
I added this code to determine if I'm already inside a transaction so I don't try to begin another:
/*******************************************************************************************
* MANAGE NESTED TRANSACTIONS - DETERMINE WHERE TRANSACTION WAS STARTED
*******************************************************************************************
IF @@TRANCOUNT = 0
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1 -- Transaction was started in this proc
END
ELSE
BEGIN
SET @TranStarted = 0 -- Transaction was started in calling proc
END
... perform the inserts/updates/deletes in the body of the sp...
Then I added this code to determine if I should commit the transaction here or elsewhere:
/***********************************************************************************
* Finished with the update, return to the caller
***********************************************************************************
IF @TranStarted = 1 -- Transaction was started in this proc so commit it here
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
RETURN
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply