Capturing Multiple Error Messages from a Single Statement Inside Try Catch

Capturing multiple error messages from a single statement inside TRY CATCH

MikeCov has answered this, but I didn't want to trust the future documentation. The future is now, so I tested this and can confirm that THROW does indeed return all the error messages.

You can reproduce this with the below script. Run each section between the comments one at a time to see the output.

/*Create tables */

CREATE TABLE dbo.test 
(
columna int primary key
)
GO

CREATE TABLE dbo.test2
(
columnb int 
)
GO

/*Create foreign key between these tables*/
ALTER TABLE dbo.test2  WITH CHECK ADD  CONSTRAINT [FK_test_to_test] FOREIGN KEY(columnb)
REFERENCES dbo.test  (columna)
GO
ALTER TABLE dbo.test2 CHECK CONSTRAINT [FK_test_to_test] 
GO

/* TEST 1 - only returns the last error message */
BEGIN TRY
    ALTER TABLE dbo.test 
    ALTER Column columna varchar
END TRY
BEGIN CATCH
    DECLARE  @ERROR_MESSAGE NVARCHAR(2048) = ERROR_MESSAGE()
    RAISERROR (@ERROR_MESSAGE,16,16)
END CATCH       

/* TEST 2 - Returns both messages, YAY */
BEGIN TRY
    ALTER TABLE dbo.test 
    ALTER Column columna varchar
END TRY
BEGIN CATCH
    THROW;
END CATCH       

/* Clean up */
DROP TABLE dbo.test2
DROP TABLE dbo.test 

How to log multiple errors in TRY..CATCH?

You can only capture a single error in a T-SQL CATCH block. According to this connect item, the workaround is to use THROW (or not use TRY/CATCH) and capture the errors in client code.

Consequently, you'll need to invoke the script from a client application (including SQLCLR) so that you can capture and log all errors.

Catching multiple errors in one try/catch

Loop over the arguments:

foreach($group in 'NA\admin','NA\dev'){
  try {
    Grant-SmbShareAccess -Name [FolderName] -AccountName $group -AccessRight Read -Force
  }
  catch {
    Write-Host "Failed to grant permission to: $group"
  }
}

Try-catch block on multiple independent statements

Try this:

;(function(fu){
    a = fu(results,'a', null/*optional default value (for when is not exists)*/);
    b = fu(results,'b');
    c = fu(results,'c');
    d = fu(results,'d');
    //Other Codes...
})(function (r, n, def){return ((r[n]||{}).data||function(){return def;})();});

Multiple or Single Try Catch

I always try to reduce levels of nesting for readability and maintainability. If you have n try/catch blocks, each handling the same type of exception, why not refactor the code that can throw the exception into methods...it would look something like:

try {
    firstBatchOfTricky();
    secondBatchOfTricky();
    ....
    nthBatchOfTricky();
} catch (ItWentBoomException e) {
   // recover from boom
} catch (ItWentBangException e) {
   // recover from bang
}

This is much more readable than having multiple try/catches. Note that your methods should describe what they do in the spirit of self documenting code.

Since you have your own Exception type, you can add the data you need to the exception to do different things in the catch block. When you say 'more specific message', you can just throw the exception with the detailed message; you shouldn't need multiple catch blocks. If you want to do drastically different things based on the state of the exception, just create more exception types and catch blocks but only one try block, as my pseudocode shows...

Finally, if you can't recover from the exception(s), you should not clutter the code with catch blocks. Throw a runtime exception and let it bubble. (Good advice from @tony in the comments)

How do you retrieve multiple SSMS query error messages from a Java Exception object in a catch statement?

If you misspell two column names, SQL Server will return only one message with the first column name that was misspelled. I believe it is is not possible to catch all errors that happen because there is only one error that happen at any given time. SQL Server will not keep trying to go ahead with the query execution if it determined that something is wrong with your query.

Catch multiple exceptions in one line (except block)

From Python Documentation:

An except clause may name multiple exceptions as a parenthesized tuple, for example

except (IDontLikeYouException, YouAreBeingMeanException) as e:
    pass

Or, for Python 2 only:

except (IDontLikeYouException, YouAreBeingMeanException), e:
    pass

Separating the exception from the variable with a comma will still work in Python 2.6 and 2.7, but is now deprecated and does not work in Python 3; now you should be using as.

---

Related Topics