If you do application development and you are used to rethrowing errors in a catch block, you may have noticed that error handling in T-SQL still doesn't support this functionality. However, you can easily emulate this functionality by rolling out your own "rethrow" stored procedure and using RAISERROR to throw the error back to the client. BOL contains a good example of that, there is a stored procedure called usp_RethrowError. It uses the built-in error functions and RAISERROR to create a new error with the same message text, number and other details.
Here is the code:
CREATE PROCEDURE [dbo].[usp_RethrowError]
AS -- Return if there is no error information to retrieve.
IF ERROR_NUMBER() IS NULL
RETURN ;
DECLARE @ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200) ;
-- Assign variables to error-handling functions that
-- capture information for RAISERROR.
SELECT @ErrorNumber = ERROR_NUMBER(), @ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(), @ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-') ;
-- Building the message string that will contain original
-- error information.
SELECT @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
'Message: ' + ERROR_MESSAGE() ;
-- Raise an error: msg_str parameter of RAISERROR will contain
-- the original error information.
RAISERROR (@ErrorMessage, @ErrorSeverity, 1, @ErrorNumber, -- parameter: original error number.
@ErrorSeverity, -- parameter: original error severity.
@ErrorState, -- parameter: original error state.
@ErrorProcedure, -- parameter: original error procedure name.
@ErrorLine-- parameter: original error line number.
) ;
GO
Sample code showing how to use it:
DECLARE @Zero INT
SET @Zero = 0
BEGIN TRY
SELECT 5 / @Zero
END TRY
BEGIN CATCH
PRINT 'We have an error...'
EXEC usp_RethrowError
END CATCH