December 14, 2005 at 6:21 am
Hi,
I am working with nested stored procedures. I have a case where because of the severity of an error in the nested sproc, it fails and aborts without executing any of the error handling code. The return code is 0. The calling sproc interprets this as success and the process carries on.
I have seen some other posts on this site that have encountered a similar situation.
My suggestion is that all sprocs that use the return value to indicate failure or success should set the return value to -1 at sproc start-up and only set it to 0 when the sproc has successfully completed its work.
I would be interested in feedback on this.
Thanks.
Regards.
December 14, 2005 at 6:36 am
I thought that the @@RETURN_VALUE is intrinsic to the RETURN statement. I didn't think you could set it at will.
I tend to use positive values greater than zero to indicate success.
Zero indicates that no action was carried out
negative numbers indicate an error.
December 14, 2005 at 6:48 am
Hi,
Ok, modifying my case slightly.
If there is a single point of exit for a successfully executed sproc, at that point the return value could be set to a value that uniquely identifies the successful execution of the sproc. If the sproc returns any other value, we know that we have had a failure even if that failure is a fatal error.
Regards,
PK.
December 14, 2005 at 7:05 am
Also remember that non fatal errors will not stop/abort a proc unless you set XACT_ABORT otherwise you will have to trap non fatal errors after each statement.
Far away is close at hand in the images of elsewhere.
Anon.
December 14, 2005 at 10:35 am
In some cases an error in a stored procedure will cause it to abort without ever setting the return code. If the variable you are using to store the return code (e.g. @return_code) contains zero from a prior successful call to a stored procedure, then its value will remain unchanged leading you to believe the failed call was successful.
Always set your return code variable to a value that indicates failure just prior to executing a call to a stored procedure.
Since zero is the default return value, I have all my procedures coded to return 1 to indicate success. Any other return value would indicate failure.
ron
December 15, 2005 at 12:42 pm
This is how I handle errors within stored procedures. The first statement is an update where I catch the error code with a SELECT and if there is an error, it prints an output telling the user where the error occurred, rolls back the transaction and returns which stops the stored procedure and returns the error code to the next level. The second statement is an example of catching the error returned by another stored procedure, and passing that error up the chain as well.
CREATE PROCEDURE USP_SOME_SPROCK( @NEW_VALUE TYPE )
AS
SET NOCOUNT ON
BEGIN
DECLARE @ERROR INT
DECLARE @FAIL VARCHAR(100)
SET @FAIL = 'Failure in USP_SOME_SPROCK: '
BEGIN TRANSACTION seme_transaction_name
UPDATE SOME_TABLE
SET SOME_COLUMN = @NEW_VALUE
WHERE SOME_FILTER = CONDITION
SELECT @ERROR = @@ERROR
IF @ERROR <> 0
BEGIN
PRINT @FAIL + 'On update of SOME_TABLE.SOME_COLUMN = ' + @NEW_VALUE + '; @ERROR = ' + CAST( @ERROR AS VARCHAR )
ROLLBACK TRANSACTION seme_transaction_name
RETURN @ERROR
END
EXECUTE @ERROR = USP_SOME_OTHER_SPROCK
IF @ERROR <> 0
BEGIN
PRINT @FAIL + 'On execution of USP_SOME_OTHER_SPROCK; @ERROR = ' + CAST( @ERROR AS VARCHAR )
ROLLBACK TRANSACTION seme_transaction_name
RETURN @ERROR
END
COMMIT TRANSACTION seme_transaction_name
RETURN @@ERROR
END
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply