How to return multiple errors in a single Query
<p>My goal is to catch error message from SQL query, log or print then pass it instead of letting it generate a real error. but I found it's not possible to catch multiple errors from the examining query; only the last error will be caught:</p>

<pre><code>DECLARE @ErrorMessage varchar(1000)
BEGIN TRY
    EXEC('SELECT AA,BB FROM TABLE')--neither column AA nor BB exists
END TRY
BEGIN CATCH
SET @ErrorMessage  = 'ERRORMESSAGE: ' + Error_Message()
PRINT @ErrorMessage
END CATCH
</code></pre>

<p>The query will only give feedback that column BB cannot found, but cannot show that AA column also doesn't exist.</p>

<p>Or another example, by putting this query in <code>TRY</code> block</p>

<pre><code>EXEC('CREATE SCHEMA abc AUTHORIZATION [dbo]') --schema abc already exists 
</code></pre>

<p>It will acutally raise error 'schema already exists' first, then another error 'cannot create schema, see previous error', but now the 1st key error containing key information has been 'eaten'.</p>

<p>How to show all of the error messages then?</p>