Sql Server 2005 error handling - inner exception

Question

In C# you can get the original error and trace the execution path (stack trace) using the inner exception that is passed up. I would like to know how this can be achieved using the error handling try/catch in sql server 2005 when an error occurs in a stored procedure nested 2 or 3 levels deep.

I am hoping that functions like ERROR_MESSAGE(), ERROR_LINE(), ERROR_PROCEDURE(), ERROR_SEVERITY() can be easily passed up the line so that the top level stored proc can access them.

Answer 1

One way you could do this would be to create an in memory table and insert rows into it when you catch an exception. You would then re-raise the exception and the next function up the chain would then have a chance to handle the exception or also log the exception to the in memory table. It's nasty, but unfortunately there doesn't seem to be a way to get the T-SQL call stack :(

Answer 2

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:

<root>
  <a>
    <ErrorMessage>Bad number set!</ErrorMessage>
    <ErrorLine>6</ErrorLine>
    <ErrorProcedure>SubProcedure</ErrorProcedure>
    <ErrorSeverity>16</ErrorSeverity>
  </a>
</root>

Enjoy