IF, RAISERROR & RETURN in Stored Procedure
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开发者_StackOverflow中文版 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?
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.
精彩评论