3

I have a stored procedure, PROC, which receives some parameters. If one of them, @ID, is not null, a given stored procedure, PROC_A, must be executed. Otherwise, PROC_B must be executed. The problem is that both of them may issue a RAISERROR, which I want to propagate through the call stack to be displayed at the client application. However, that RAISERROR won't stop the rest of the PROC stored procedure as it should, and, since I am using an IF clause, checking IF ( @@ERROR <> 0 ) RETURN isn't an option either. My only choice seems to be using a TRY...CATCH block to wrap the IF clause and rethrow the RAISERROR from within the CATCH block, which is awkwards because then I will have to cache ERROR_MESSAGE(), ERROR_SEVERITY() and ERROR_STATE() and use RAISERROR once again.

Isn't there really any more elegant way?

User
  • 3,244
  • 8
  • 27
  • 48

1 Answers1

8

just use a TRY - CATCH block and echo back the original error, which isn't that hard to do:

BEGIN TRY
    --used in the CATCH block to echo the error back
    DECLARE @ErrorMessage nvarchar(400), @ErrorNumber int, @ErrorSeverity int, @ErrorState int, @ErrorLine int

    --Your stuff here

END TRY
BEGIN CATCH

    --your error code/logging here

    --will echo back the complete original error message
    SELECT @ErrorMessage = N'Error %d, Line %d, Message: '+ERROR_MESSAGE(),@ErrorNumber = ERROR_NUMBER(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE(),@ErrorLine = ERROR_LINE()
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber,@ErrorLine)

END CATCH

Also, it is best practice to have your entire procedure in a TRY - CATCH, and not just the external procedure calls.

KM.
  • 101,727
  • 34
  • 178
  • 212
  • 3
    +1 And I'll shamelessly link to my template http://stackoverflow.com/questions/2073737/nested-stored-procedures-containing-try-catch-rollback-pattern/2074139#2074139 – gbn Jul 01 '11 at 16:48