How to ignore errors from nested stored procedures in a SQL Server 2000 procedure called from ASP

I am working on a "classic" ASP app开发者_StackOverflowlication with a SQL Server 2000 database.

We have a stored procedure (let's call it SP0) that calls other stored procedures (let's say SP0.1, SP0.2 ...) which themselves call another stored procedure called SPX.

All those procedures generate errors when something goes wrong using RAISERROR().

We want to be able to launch SP0 with a parameter @errorsInResultSet which will change its behaviour : instead of "re-raising" the errors as it does so far, each sub-procedure will log the errors in a temporary table #detectedProblems and return it at the end.

Adding errors to the temporary table is not a problem, but I can not figure out how to ignore the errors generated by the nested stored procedures.

I have done this so far :

EXEC @rc = [SP0.1] @errorsAsResultSet = @errorsAsResultSet  
    IF (0 <> @@ERROR) OR (0 <> @rc)
    BEGIN
        IF (@errorsAsResultSet <> 0x1)
        BEGIN
            RAISERROR('SP0.1: Error for table Tests in db %s.%s', 16, 1, @@SERVERNAME, @db)
        END
        GOTO FAILURE
    END

This works fine, but it still generate errors from the lowest SPX, which prevent it from being executed by ADO in classic ASP.

How can I ignore the errors ?

If you're happy that the errors are being logged and it's safe to continue, you can use ON ERROR RESUME NEXT on the line before the SP call. This will prevent the page from throwing errors.

To turn back on errors later in the page, you can use ON ERROR GOTO 0

In the end, it looks like there is no way to "hide" messages generated by PRINT or RAISERROR statements in SP0.1, SP0.2 from the calling Stored Procedure, which means that the execution is always interpreted as "erroneous" by ASP.

In the end, I rewrote a new Stored Procedure with a special parameter to configure how to report errors.