How to rethrow the same exception in SQL Server

THROW vs RAISERROR: The Great Debate

In the exciting world of SQL Server error handling, the THROW keyword is a newer kid on the block, introduced in SQL Server 2012. The top selling point? THROW's stunning ability to rethrow the original exception complete with all its glory — context, message, severity, state... and the kitchen sink! On the other side, RAISERROR works swell but needs explicit input for these parameters. The risk? Original error context could take a hike.

Managing Transactions like a Pro

Inside a magical TRY...CATCH block, gentle management of transactions is a sacred task. More so when performing a series of related operations that should either all succeed or all fail:

BEGIN TRY
    BEGIN TRANSACTION;
    -- Conjuring up some database magic
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
         -- Nothing to see here; moving along, folks!
        ROLLBACK TRANSACTION;
    THROW; -- The magic trick gone wrong, back to square one!
END CATCH


Call upon @@TRANCOUNT to peep over the fence for any lingering transactions before committing or rolling back.

Retrieving Error Information for Dummies

In order to seize useful error information for your frontend to display, ERROR_MESSAGE(), ERROR_NUMBER(), and ERROR_PROCEDURE() are your trusty friends within the CATCH block:

BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage,
        ISNULL(ERROR_PROCEDURE(), '-') AS ErrorProcedure;
    THROW; -- Rethrow: Ta-da! Error back in context
END CATCH


Keeping SQL Server Engine Errors on a Short Leash

Keep engine-generated errors, especially those numbered below Narnia's door (50000), in check. All user-defined errors should climb the number ladder above 50000 to avoid muddling up the waters.

Pre-SQL Server 2012? No Problem

Working with a veteran version prior to SQL Server 2012? Fear not, for we have a workaround to imitate the rethrow behavior. Brace yourself, we're about to RAISERROR:

BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorNumber INT;
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
    -- "I've got your error right here!"
END CATCH


A little verbose, perhaps. But hey, who said prehistoric SQL was all sunshine and rainbows?

Anticipating Future SQL Server Goodies

Be prepared for likely improvements for more specific error catching abilities as SQL Server keeps evolving. Finer control over exception handling and tighter integration with frontend languages could be just around the corner. Gear up for a more resilient application!