Sql Server 2005 Error Handling - Internal Exception

Question

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

I hope that functions such as ERROR_MESSAGE (), ERROR_LINE (), ERROR_PROCEDURE (), ERROR_SEVERITY () can be easily passed along the line so that the upper level stored in proc can access them.

Answer 1

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

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 returns 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

Answer 2

One way to do this is to create a table in memory and insert rows into it when you catch the exception. Then you raise the exception again, and the next function up the chain will be able to handle the exception or also register the exception in the memory table. This is unpleasant, but unfortunately there is no way to get the T-SQL call stack :(