从 TRY CATCH 中的单个语句捕获多个错误消息 [英] Capturing multiple error messages from a single statement inside TRY CATCH

问题描述

我正在对多个列和表运行一批语句，并希望检索有关发生什么错误的信息.

I am running a batch of statements on several columns and tables and want to retrieve information on what errors occur.

该语句是一种类型更改(varchar 到 nvarchar)，当它失败时，它似乎返回 2 个错误.

The statement is a type change (varchar to nvarchar) and when it fails, it seems to return 2 errors.

消息 5074，级别 16，状态 1，第 1 行对象DF_XXX_YYY"是依赖于YYY"列.

Msg 5074, Level 16, State 1, Line 1 The object 'DF_XXX_YYY' is dependent on column 'YYY'.

消息 4922，级别 16，状态 9，第 1 行 ALTERTABLE ALTER COLUMN 描述失败，因为一个或多个对象访问此列.

Msg 4922, Level 16, State 9, Line 1 ALTER TABLE ALTER COLUMN Description failed because one or more objects access this column.

但是，当我将它包装在 TRY/CATCH 块中并选择 ERROR_MESSAGE() 时，它只返回第二个错误:

However, when I wrap it in a TRY/CATCH block, and select ERROR_MESSAGE(), it only returns the second error:

ALTER TABLE ALTER COLUMN 描述失败，因为一个或多个对象访问此列.

ALTER TABLE ALTER COLUMN Description failed because one or more objects access this column.

理想情况下，我会让它返回第一条消息，因为这会提供更多信息.

Ideally I would have it return the first message, as this is much more informative.

确切的 SQL 语句是:

The exact SQL statement is:

begin try
    alter table XXX
    alter column YYY
    nvarchar(200)
end try
begin catch
    select ERROR_MESSAGE(), ERROR_LINE(), ERROR_NUMBER(), ERROR_PROCEDURE(), ERROR_SEVERITY(), ERROR_STATE()
end catch

有谁知道我如何检索更多信息?(@@ERROR 也返回第二个错误)

Does anyone know how I can retrieve the more informative message? (@@ERROR also returns the second error)

推荐答案

MikeCov 已经回答了这个问题，但我不想相信未来的文档.未来就在眼前，所以我对此进行了测试，可以确认 THROW 确实返回了所有错误消息.

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 

这篇关于从 TRY CATCH 中的单个语句捕获多个错误消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！