CODEWITHSUNDEEP
sql-server-2005error-handling
SRAMPI
SRAMPI

Reputation: 373

Sql Server 2008 Try Catch Error collection

I want to get the collection of errors, caused by a stored procedure, from an outer stored procedure. Is there a way to get that.

The samples are below. I get the first error 80125. I would also like to get the information about second one 80054. Is there a collection or server variable in sql server (I do know how to get this from .Net)

BEGIN TRY

DECLARE @RC int

EXEC @RC= TestErrorProc

END TRY


BEGIN CATCH

   SELECT

    ERROR_NUMBER() AS ErrorNumber,ERROR_SEVERITY() AS ErrorSeverity

    ,ERROR_STATE() AS ErrorState    ,ERROR_PROCEDURE() AS ErrorProcedure

    ,ERROR_LINE() AS ErrorLine    ,ERROR_MESSAGE() AS ErrorMessage

END CATCH

--Gives the following 80125 15 1 TestErrorProc 11 Database Custom Error: Procedure:PROCEDURENAME; User:dbo; Workstation:VENTURE - Ambiguous error resolution - The following error was caused by the fifth potentially invalid parameter

CREATE PROCEDURE [dbo].[TestErrorProc]

AS

    DECLARE @intErr int, @intRows int, @strUser varchar(64), @strWks varchar(64)

    DECLARE @strTraceMsg varchar(255)

    DECLARE @intRet int

    SELECT @strUser = USER_NAME(), @strWks = HOST_NAME()

    SET @strTraceMsg = ' Row 1'

       BEGIN

       --Ambiguous Testing

        RAISERROR( 80125, 15, 1, 'PROCEDURENAME', @strUser, @strWks, 'TABLENAME-Column information' )
          RAISERROR( 80054, 15, 1, 'PROCEDURENAME', @strUser, @strWks, @strTraceMsg, 'Issue details - business Error' )

        RETURN( -100 )

      END

Upvotes: 0

Views: 2805

Answers (1)

Ed Harper
Ed Harper

Reputation: 21505

This isn't possible without more code - once the first error has been thrown, the inner procedure exits and the code in the CATCH block of is executed.

If you need to trap several errors before returning them to the outer code, one solution would be to write more complex error handling in the inner procedure, wrapping each error in its own TRY...CATCH block and inserting the error(s) into a temporary table before throwing a new error to return control to the outer code, which could retrieve and display the contents of the temporary table. This approach should work but is likely to be quite difficult to maintain.

Upvotes: 1

Related Questions