I recently encountered a question asking how to bubble up errors from sub procedures the way that they bubble up in C#. There is no good way to do that but this is what I came up with:

The best way to handle this is using OUTPUT parameters and XML. The sample code below will demonstrate how and you can modify what you do with the XML in the TopProcedure to better handle your response to the error.

USE tempdb
go
CREATE PROCEDURE SubProcedure @RandomNumber int, @XMLErrors XML OUTPUT
AS
BEGIN
BEGIN TRY
    IF @RandomNumber > 50
        RaisError('Bad number set!',16,1)
    else
        select @RandomNumber
END TRY
BEGIN CATCH
    SET @XMLErrors = (SELECT * FROM (SELECT ERROR_MESSAGE() ErrorMessage,
        ERROR_LINE() ErrorLine, ERROR_PROCEDURE() ErrorProcedure,
        ERROR_SEVERITY() ErrorSeverity) a FOR XML AUTO, ELEMENTS, ROOT('root'))
END CATCH
END
go

CREATE PROCEDURE TopProcedure @RandomNumber int
AS
BEGIN
    declare @XMLErrors XML
    exec SubProcedure @RandomNumber, @XMLErrors OUTPUT
    IF @XMLErrors IS NOT NULL
        select @XMLErrors
END

go
exec TopProcedure 25
go
exec TopProcedure 55
go
DROP PROCEDURE TopProcedure
GO
DROP PROCEDURE SubProcedure
GO   

The initial call to TopProcedure will return 25. The second will return an XML block that looks like this:

   
     
        Bad number set!
        6
        SubProcedure
        16