title: Sql Server 2008 Try Catch Error collection
body: <p>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.</p>
  
  <p>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)</p>
  
  <hr>
  
  <pre><code>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
  </code></pre>
  
  <hr>
  
  <p>--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</p>
  
  <hr>
  
  <pre><code>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
  </code></pre>