Unfortunately, RAISERROR in a CATCH block doesn't work like you want it to work (as I understand your scenario):

1. An error occurs in a TRY block and passes control to the CATCH block.

2. Some processing occurs in the CATCH block.

3. A RAISERROR statement raises an error message to the calling application that reflects the original error and processing halts.

From Books Online:

That's how RAISERROR works in a TRY block (or in code without a TRY...CATCH construct) - it just forces an error situation.

Once you're in the CATCH block, though, RAISERROR behaves differently depending on how you use it.

Here's the basic code that is used to demonstrate three scenarios for using RAISERROR in a CATCH block:

BEGIN TRY

SELECT 1/0 AS col1

END TRY

BEGIN CATCH

DxECLARE @ErrorMessageLong NVARCHAR(4000) = N'Msg% 0.0d, Level% 0.0d, State% 0.0d, Line% 0.0d

% 1.255s'

,@ErrorMessageShort NVARCHAR(4000) = ERROR_MESSAGE()

,@ErrorSeverity INT = ERROR_SEVERITY()

,@ErrorState INT = ERROR_STATE()

,@ErrorNumber INT = ERROR_NUMBER()

,@ErrorLine INT = ERROR_LINE()

-- RAISERROR statements will go here --

END CATCH

The string value assigned to the @ErrorMessageLong variable includes a bunch of codes that instruct the RAISERROR statement to substitute the arguments that begin after the [state] argument for the "%" signs, in the order they're listed. The characters after the "%" sign provide formatting instructions. For example, the code "% 0.0d" means substitute the appropriate argument from the list in this position("%"), pad it with spaces if necessary (" "), print the value with no padding if it is longer than this width ("0"), print all digits of the value (".0"), and format it as a signed integer ("d"). It usually takes me some trial and error to get the string just right to produce the output I expect.

Here are the three different ways you can use RAISERROR in the CATCH block.

Scenario 1 - Raise an error with Msg 50000, the severity level and state of the original error, the line number of the RAISERROR statement, and the description only of the original error message and continue processing. Using this RAISERROR statement:

RAISERROR(@ErrorMessageShort, @ErrorSeverity, @ErrorState)

with the code above will return this error message:

and processing will continue (add some code after the RAISERROR statement in the CATCH block to see this is true).

Scenario 2 - Raise an error with Msg 50000, a specified severity level and state, the line number of the RAISERROR statement, and the full text of the original error message and stop processing if warranted by the specified severity level. Using this RAISERROR statement:

RAISERROR(@ErrorMessageLong, 17, 1, @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorMessageShort)

raises this error:

Processing will stop if the specified severity level is greater than 16 (only sysadmins can specify a severity level higher than 18 and only when using the WITH LOG option).

Scenario 3:

Print a message that looks like the original error message and continue processing. Using this RAISERROR statement:

RAISERROR(@ErrorMessageLong, -1, -1, @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorLine, @ErrorMessageShort)

returns this message (it's not a true error message, just a message like the output of a PRINT statement):

and processing will continue.

That's supremely frustrating, but unfortunately, there doesn't seem to be a good workaround to achieve the desired result in SQL Server 2008R2 and earlier versions.

Starting with SQL Server 2012, the THROW statement provides this functionality. When executed in a CATCH block, THROW returns the error that caused control to drop to the CATCH block and halts processing if warranted by the severity level. On a SQL Server 2012 instance, executing the THROW statement in place of the RAISERROR statement in the code above, like this (it requires that the previous statement be terminated by a semicolon):

;

THROW

raises this error message:

and processing halts. It's a shame that something this simple isn't available in earlier versions!