Exception flow in a stored procedure calling another stored procedure

Question

I've got a stored procedure that calls another stored procedure. SP2 has a try/transaction/catch/rollback/raiserror. If SP2 raises an error, will it bubble up through SP1 to the caller, or do I have to nest the call to SP2 in a try/catch as well? If the latter is the case, how can I assure that I'm not killing the "stack trace" of the error from SP2 when raising/exiting SP1?

-- will this bubble any error from SP2 and exit SP1 as well?

EXEC dbo.storedProc2 @someParameter = @someValue 

--Or do I need to do this?

BEGIN TRY
    EXEC dbo.storedProc2 @someParameter = @someValue
END TRY
BEGIN CATCH
    -- this is what I normally do in a simple catch/raise scenario:
    -- will it kill the error stack?
    DECLARE @ErrMsg VARCHAR(4000), @ErrSeverity INT, @ErrState INT, @ErrLine INT
    SELECT @ErrMsg = ERROR_MESSAGE() + '  Line %d', 
           @ErrSeverity = ERROR_SEVERITY(), 
           @ErrState = ERROR_STATE(),
           @ErrLine = ERROR_LINE()
    RAISERROR(@ErrMsg, @ErrSeverity, @ErrState, @ErrLine)
END CATCH

Answer 1

You will need to have an outer TRY/CATCH block.

If you only have the error trapping in SP2, when SP2 errors it will abort.
It will then pass the error code back to SP1, but SP1 will continue to execute under most circumstances (exception would be a fatal error with severity 20-25).

There's no real inherent error trapping without using TRY/CATCH (or checking @@Error if you are old-school) - the code will continue to execute, for better or worse.