Bug in MS-SQL Server 2008 R2?

[everhett.raman]

Hi,

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

Question:
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.

Rgds,
Everhett

[Momerath]

Did you try properly ending the SQL statements with a ';' character?

[everhett.raman]

Hi,

I tried appending semicolon to every lines, but it still executes the next line. Btw, is semicolon 'the' mandatory end-of-statement in Ms-SQL? Just wondering...

[everhett.raman]

Hi,

Looks like there is no much help on this topic. Any comments will be appreciated.

[huangzhi]

1. All lines are execute. Not just 1 line below the. If you want to exit, use severity more than 10 (> 10).
2. No comment
3. I am not use TRY CATCH. I usually use RAISERROR alone with severity 16. So the error will be send to my application immediately.

[everhett.raman]

1. No all lines are executed. If it does, i would have made my conclusion that execution does not terminate. But surprisingly it does after the last 1 line.
2. This shows, try...catch in MSSQL is not the solution for the @@ERROR problem we faced.

[fx1250s]

Actually, the code seems to be behaving properly. Where is the error thrown? I don't see any code that would cause an error. The reason the first line in the TRY block prints and not the remaining lines is that, since no error occurred, all of the error functions return NULL. Thus, nothing prints.

As one of the other posters mentioned, RAISERROR doesn't terminate a batch. You would need to follow it with RETURN.

As a test, surround all of the error functions with IsNull() or Coalesce().