How to get call stack info in SQL Server's catch block?

Question

When I have multiple errors in my query, and I use try catch and error_message() function in my catch block, I only get the last error message.

For example, if my errors are:

Msg 2714, Level 16, State 5, Line 14
There is already an object named 'IX_EntityTypeConfigs' in the database.
Msg 1750, Level 16, State 1, Line 14
Could not create constraint or index. See previous errors.

in the catch block I just get:

Could not create constraint or index. See previous errors.

How can I get the whole message?

I have searched and saw that this is a limitation of SQL Server, but articles belonged to 2014 and older than that.

I wonder if things has changed since.

Answer 1

I believe it's still correct. The recommended way is to rethrow the errors to the application via the THROW command and let the app handle it.

The best resource is still this article by Erland Sommarskog - Error and Transaction Handling in SQL Server

You can also capture the errors with Extended Events (XE) error_reported (on SQL Server side). That would show you all the errors that were fired.

I blog about it here: Investigating Errors With Extended Events.