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?

In fact, I think it catches the first error not the last. Unless you recover from the error, the engine cannot continue processing. — Gordon Linoff
– Gordon Linoff, Commented Jun 30, 2016 at 2:45
@GordonLinoff is correct. Once the first error is thrown the engine won't process further. So you don't have to worry about catching multiple errors. — Sam
– Sam, Commented Jun 30, 2016 at 3:27
@GordonLinoff. Yes if there are multiple queries, it will stop at the first query; but my thing is a single query generate multiple errors (just like my expample, creating schema or using SP or EXEC(@query) that the containing query selects two invalid columns at once). — a4194304
– a4194304, Commented Jun 30, 2016 at 3:44
What version of SQL are you using? Can you use THROW instead? ERROR_MESSAGE() is limited in SQL server to just the last error that was thrown. — ZLK
– ZLK, Commented Jun 30, 2016 at 4:53

clifton_h · Accepted Answer · 2016-06-30 06:35:22Z

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.

edited Jun 30, 2016 at 6:35

answered Jun 30, 2016 at 6:27

clifton_h

1,2988 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

clifton_h Over a year ago

well, if you do not have abc as a schema yet, it runs through. But you get the idea. :)

IVNSTN · Accepted Answer · 2016-06-30 05:25:27Z

0

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.

edited Jun 30, 2016 at 5:25

answered Jun 30, 2016 at 4:58

IVNSTN

9,3896 gold badges24 silver badges42 bronze badges

5 Comments

clifton_h Over a year ago

He can and probably should still use a TRY-Block for severe fatal errors. Error levels can be set under 11, which will not transfer the call to the catch block. MSDN-RAISERROR

a4194304 Over a year ago

@IvanStarostin thanks for giving such a thinking. But by my mentioning print I actually mean the code PRINT @errormsg so that it's equivalent to capturing it and log. any other solutions

a4194304 Over a year ago

@clifton_h I think this is a good idea, but how do I set error level less than 11 even actually it is a common Lvl 15 or 16 one?

clifton_h Over a year ago

Use RAISERROR. You can set the severity there. However, you are limited on FATAL errors if you do not have sys admin rights.

IVNSTN Over a year ago

@clifton_h do this on client-side.

Community · Accepted Answer · 2017-05-23 12:32:56Z

0

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

edited May 23, 2017 at 12:32

CommunityBot

11 silver badge

answered Jun 30, 2016 at 9:12

Y.B.

3,58617 silver badges24 bronze badges

Collectives™ on Stack Overflow

How to return multiple errors in a single Query

3 Answers 3

1 Comment

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related