How to return multiple errors in a single Query

Question

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:

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

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

Or another example, by putting this query in TRY block

EXEC('CREATE SCHEMA abc AUTHORIZATION [dbo]') --schema abc already exists 

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'.

How to show all of the error messages then?

Answer 1

• YOU CAN STILL USE RAISERROR INSIDE TRY-CATCH BLOCKS

Ivan is right about ERROR_MESSAGE and how TRY-CATCH may remove the robust nature of your query, however, this only occurs when the SEVERITY of the message is above 10 in a TRY block. So the trick is to set the severity under 11.

The error is returned to the caller if RAISERROR is run:

• Outside the scope of any TRY block.
• With a severity of 10 or lower in a TRY block.
• With a severity of 20 or higher that terminates the database connection.

MSDN - RAISERROR

RAISERROR can be used as a substitute for PRINT and allows for custom messages. Furthermore, you can set the STATE to different numbers to keep track of similar, but different errors in your code.

Since Fatal errors will be your bane, I suggest you test queries and DDL commands before running them. For example, instead of blindly attempting EXEC('CREATE SCHEMA abc AUTHORIZATION [dbo]'), you can try this ad-hoc message instead:

DECLARE @SCHEMA NVARCHAR(10) 
DECLARE @Message NVARCHAR(255)
SET @SCHEMA = N'abc'
SET @Message = N'The Schema ' + @SCHEMA + ' already exists.'

IF SCHEMA_ID(@SCHEMA) IS NOT NULL
EXEC('CREATE SCHEMA abc AUTHORIZATION [dbo]')
ELSE RAISERROR(@Message, 10, 1)
--result: The Schema abc already exists.

There are many ways of checking the validity of dynamic SQL, DDL, and DML, including useful functions like OBJECT_ID, OBJECT_NAME, DATABASE_ID, etc where you test safely, and then run the appropriate RAISERROR message for each error.

Answer 2

Remove TRY-CATCH, if possible - divide script statements into many separate batches with GO.

TRY-CATCH reacts on first exception and breaks execution of TRY-block:

If an error occurs in the TRY block, control is passed to another group of statements that is enclosed in a CATCH block.

https://msdn.microsoft.com/en-us/library/ms175976.aspx

So behaviour of TRY-CATCH is rather opposite to your intention.

GO sets the end of the batch. Many of errors don't even break the batch, because they have low severity, so for some cases there is no need even to split script into many batches.

As an example here is sample dummy script for testing or some utility purpose (not for production of course) that generates many errors:

create proc SomeProc as
begin
  exec('select uknown from non_existent')
end
GO
drop table #test1
drop table #test2
GO
drop table #test3
GO
create table #test1 (id int primary key)

insert into #test1(id)
exec SomeProc

insert into #test
values (1)

insert into #test1
values (1)
GO
insert into #test1
values (11)

insert into #test1
values (11)

insert into #test
values (22)
GO
select * from #test1
GO
drop table #test
GO
drop table #test
drop proc SomeProc
select object_id('SomeProc', 'P')
GO

it does give the output of selects:

and all the messages:

Msg 3701, Level 11, State 5, Line 7 Cannot drop the table '#test2', because it does not exist or you do not have permission.

Msg 3701, Level 11, State 5, Line 9 Cannot drop the table '#test3', because it does not exist or you do not have permission.

Msg 208, Level 16, State 1, Line 11 Invalid object name 'non_existent'.

(0 row(s) affected)

Msg 208, Level 16, State 0, Line 16 Invalid object name '#test'.

(1 row(s) affected)

Msg 2627, Level 14, State 1, Line 25 Violation of PRIMARY KEY constraint 'PK__#test1____3213E83FF35979C1'. Cannot insert duplicate key in object 'dbo.#test1'. The duplicate key value is (11). The statement has been terminated.

Msg 208, Level 16, State 0, Line 28 Invalid object name '#test'.

(1 row(s) affected)

Msg 3701, Level 11, State 5, Line 33 Cannot drop the table '#test', because it does not exist or you do not have permission.

Msg 3701, Level 11, State 5, Line 35 Cannot drop the table '#test', because it does not exist or you do not have permission.

"My goal is to catch error message from SQL query, log or print then pass it instead of letting it generate a real error." - if "print" is ok then just remove TRY-CATCH.

Answer 3

Run the script through sqlcmd and redirect errors to a file: How to get SQLCMD to output errors and warnings only.

sqlcmd -i Script.sql -E -r1 1> NUL