Over at the SQL Server Central forums, I just answered a question about the RAISERROR function in SQL Server 2008R2 and earlier versions. The original poster wanted to use RAISERROR in a CATCH block (after some logging, etc.) to return the original error to the calling application and halt further processing. Unfortunately, RAISERROR behaves much differently when used in the CATCH block of a TRY…CATCH construct than it does in a TRY block or in code without a TRY…CATCH construct, and this behavior is somewhat different than might be expected – it simply can’t do what the original posted wanted it to do. In SQL Server 2012 and later versions, THROW does exactly what the original poster wants to do when used in a CATCH block, but it’s a shame that there’s no simple workaround to achieve the same result in earlier versions.
Using RAISERROR in SQL Server 2008R2 and Earlier Versions