How to rethrow the same exception in SQL Server

I think your choices are:

• Dont catch the error (let it bubble up)
• Raise a custom one

At some point, SQL will probably introduce a reraise command, or the ability to catch only certain errors. But for now, use a workaround. Sorry.

Here is a fully functional clean code sample to rollback a series of statements if an error occurs and reports the error message.

begin try
    begin transaction;

    ...

    commit transaction;
end try
begin catch
    if @@trancount > 0 rollback transaction;
    throw;
end catch

Before SQL 2012

begin try
    begin transaction;
    
    ...
    
    commit transaction;
end try
begin catch
    declare @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int;
    select @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
    if @@trancount > 0 rollback transaction;
    raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
end catch

SQL 2012 introduces the throw statement:

http://msdn.microsoft.com/en-us/library/ee677615.aspx

If the THROW statement is specified without parameters, it must appear inside a CATCH block. This causes the caught exception to be raised.

BEGIN TRY
    BEGIN TRANSACTION
    ...
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    THROW
END CATCH

Rethrowing inside the CATCH block (pre-SQL2012 code, use THROW statement for SQL2012 and later):

DECLARE
    @ErrorMessage nvarchar(4000) = ERROR_MESSAGE(),
    @ErrorNumber int = ERROR_NUMBER(),
    @ErrorSeverity int = ERROR_SEVERITY(),
    @ErrorState int = ERROR_STATE(),
    @ErrorLine int = ERROR_LINE(),
    @ErrorProcedure nvarchar(200) = ISNULL(ERROR_PROCEDURE(), '-');
SELECT @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 'Message: ' + @ErrorMessage;
RAISERROR (@ErrorMessage, @ErrorSeverity, 1, @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine)