As i was experimenting stored procedure's TRY-CATCH with RAISERROR, came across this issue. Would like some opinion on this.
Microsoft sites documented as follows:
SQL Error 0 - 10: caught in the TRY-Block, i.e. will not reach CATCH-block.
SQL Error 11 - 19: caught in CATCH-Block
SQL Error 20 - 25: fatal
This means when error 0 to 10 raised in TRY-block, it will return the error to caller and exit. Try this simple code below.
CREATE PROCEDURE sp_PrimeTestStub AS BEGIN DECLARE @CodeBlockName NVARCHAR(20); SET @CodeBlockName = 'TRY : ' BEGIN TRY -- Error raised from body RAISERROR ('Error raised in body.' -- Message text. ,9 -- Severity. ,1 -- State. ) PRINT @CodeBlockName + 'BLOCK' PRINT @CodeBlockName + CAST(ERROR_NUMBER() AS NVARCHAR(9)) PRINT @CodeBlockName + ERROR_MESSAGE(); PRINT @CodeBlockName + CAST(ERROR_SEVERITY()AS NVARCHAR(9)) PRINT @CodeBlockName + CAST(ERROR_STATE()AS NVARCHAR(9)) PRINT @CodeBlockName + ERROR_PROCEDURE(); PRINT @CodeBlockName + CAST(ERROR_LINE()AS NVARCHAR(9)) END TRY BEGIN CATCH END CATCH END
Undesirable Output upon sp call:
Error raised in body.
Msg 50000, Level 9, State 1
TRY : BLOCK
1. Why the next one line is allowed to be executed upon error-raise? Shouldn't it return to caller and exit?
2. Error 21, still executed in CATCH if raised in TRY. Is this how it should handle the fatal error?
3. The TRY..CATCH seems mediocre method to trap and report error. Saying this because, if error raised with 0-10 in TRY-block (unexpected, an execution error), totally out of developer's control to handle the error. Is there any better concept?
Really would appreciate ideas or debate on this issue. Thank you.