SQL 如何在sql server中重新抛出相同的异常

how to rethrow same exception in sql server

提问by Shantanu Gupta

I want to rethrow same exception in sql server that has been occured in my try block. I am able to throw same message but i want to throw same error.

我想在我的 try 块中发生的 sql server 中重新抛出相同的异常。我能够抛出相同的消息，但我想抛出相同的错误。

BEGIN TRANSACTION
    BEGIN TRY
            INSERT INTO Tags.tblDomain 
            (DomainName, SubDomainId, DomainCode, Description)
            VALUES(@DomainName, @SubDomainId, @DomainCode, @Description)
            COMMIT TRANSACTION
    END TRY

    BEGIN CATCH
            declare @severity int; 
            declare @state int;

            select @severity=error_severity(), @state=error_state();

            RAISERROR(@@Error,@ErrorSeverity,@state);
            ROLLBACK TRANSACTION
    END CATCH

RAISERROR(@@Error, @ErrorSeverity, @state);

RAISERROR(@@Error, @ErrorSeverity, @state);

This line will show error, but i want functionality something like that. This raises error with error number 50000, but i want erron number to be thrown that i am passing @@error,

这一行将显示错误，但我想要类似的功能。这会引发错误号为 50000 的错误，但我希望抛出我正在通过的错误号@@error，

I want to capture this error no at frontend

我想在前端捕获此错误 no

i.e.

IE

catch (SqlException ex)
{
if ex.number==2627
MessageBox.show("Duplicate value cannot be inserted");
}

I want this functionality. which can't be achieved using raiseerror. I dont want to give custom error message at back end.

我想要这个功能。这是使用 raiseerror 无法实现的。我不想在后端提供自定义错误消息。

RAISEERROR should return below mentioned error when i pass ErrorNo to be thrown in catch

当我通过 ErrorNo 在 catch 中抛出时，RAISEERROR 应该返回下面提到的错误

Msg 2627, Level 14, State 1, Procedure spOTest_DomainInsert,

Line 14 Violation of UNIQUE KEY constraint 'UK_DomainCode'. Cannot insert duplicate key in object 'Tags.tblDomain'. The statement has been terminated.

Msg 2627, Level 14, State 1, Procedure spOTest_DomainInsert,

第 14 行违反 UNIQUE KEY 约束“UK_DomainCode”。无法在对象“Tags.tblDomain”中插入重复键。该语句已终止。

EDIT:

编辑：

What can be the drawback of not using try catch block if i want exception to be handled at frontend considering stored procedure contains multiple queries that need to be executed

考虑到存储过程包含多个需要执行的查询，如果我想在前端处理异常，那么不使用 try catch 块的缺点是什么

回答by Michael

SQL 2012 introduces the throw statement:

SQL 2012 引入了 throw 语句：

http://msdn.microsoft.com/en-us/library/ee677615.aspx

http://msdn.microsoft.com/en-us/library/ee677615.aspx

If the THROW statement is specified without parameters, it must appear inside a CATCH block. This causes the caught exception to be raised.

如果指定的 THROW 语句不带参数，则它必须出现在 CATCH 块内。这会导致引发捕获的异常。

BEGIN TRY
    BEGIN TRANSACTION
    ...
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    THROW
END CATCH

回答by Ben Gripka

Here is a fully functional clean code sample to rollback a series of statements if an error occurs and report the error message.

这是一个功能齐全的干净代码示例，用于在发生错误时回滚一系列语句并报告错误消息。

begin try
    begin transaction;

    ...

    commit transaction;
end try
begin catch
    declare @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int;
    select @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
    rollback transaction;
    raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
end catch

回答by nzeemin

Rethrowing inside the CATCH block (pre-SQL2012 code, use THROW statement for SQL2012 and later):

在 CATCH 块内重新抛出（SQL2012 之前的代码，SQL2012 及更高版本使用 THROW 语句）：

DECLARE
    @ErrorMessage nvarchar(4000) = ERROR_MESSAGE(),
    @ErrorNumber int = ERROR_NUMBER(),
    @ErrorSeverity int = ERROR_SEVERITY(),
    @ErrorState int = ERROR_STATE(),
    @ErrorLine int = ERROR_LINE(),
    @ErrorProcedure nvarchar(200) = ISNULL(ERROR_PROCEDURE(), '-');
SELECT @ErrorMessage = N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 'Message: ' + @ErrorMessage;
RAISERROR (@ErrorMessage, @ErrorSeverity, 1, @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine)

回答by Rob Farley

I think your choices are:

我认为你的选择是：

• Dont catch the error (let it bubble up)
• Raise a custom one

• 不要发现错误（让它冒泡）
• 举一个自定义的

At some point, SQL will probably introduce a reraise command, or the ability to catch only certain errors. But for now, use a workaround. Sorry.

在某些时候，SQL 可能会引入 reraise 命令，或者仅捕获某些错误的能力。但是现在，请使用解决方法。对不起。

回答by gbn

You can't: only the engine can throw errors less than 50000. All you can do is throw an exception that lookslike it...

你不能：只有引擎可以抛出小于 50000 的错误。你所能做的就是抛出一个看起来像这样的异常......

See my answer here please

请在此处查看我的答案

The questioner here used client side transactions to do what he wanted which I think is a wee bit silly...

这里的提问者使用客户端事务来做他想做的事情，我认为这有点愚蠢......

回答by Ashish Gupta

Ok, this is a workaround...:-)

好的，这是一种解决方法...:-)

DECLARE @Error_Number INT
BEGIN TRANSACTION 
    BEGIN TRY
    INSERT INTO Test(Id, Name) VALUES (newID(),'Ashish') 
    /* Column 'Name' has unique constraint on it*/
    END TRY
    BEGIN CATCH

            SELECT ERROR_NUMBER()
            --RAISERROR (@ErrorMessage,@Severity,@State)
            ROLLBACK TRAN
    END CATCH

If you note the catch block, It is not raising the error but returning the actual error number (and also would rollback the transaction). Now in your .NET code, instead of catching the exception, if you use ExecuteScalar(), you get the actual error number you want and show the appropriate number.

如果您注意到 catch 块，它不会引发错误，而是返回实际的错误号（并且还会回滚事务）。现在，在您的 .NET 代码中，如果您使用 ExecuteScalar()，您将获得所需的实际错误编号并显示适当的编号，而不是捕获异常。

int errorNumber=(int)command.ExecuteScalar();
if(errorNumber=<SomeNumber>)
{
    MessageBox.Show("Some message");
}

Hope this helps,

希望这可以帮助，

EDIT :- Just a note, If you want to get the number of records affected and trying to use ExecuteNonQuery, the above solution may not work for you. Otherwise, I think It would suit what you need. Let me know.

编辑：- 请注意，如果您想获得受影响的记录数并尝试使用 ExecuteNonQuery，上述解决方案可能对您不起作用。否则，我认为它会适合您的需要。让我知道。

回答by Sergey

You can also create a wrapper stored procedure for the those scenarios when you want the SQL statement to be executed within the transaction and feed the error up to your code.

当您希望在事务中执行 SQL 语句并将错误提供给您的代码时，您还可以为这些场景创建包装器存储过程。

CREATE PROCEDURE usp_Execute_SQL_Within_Transaction
(
    @SQL nvarchar(max)
)
AS

SET NOCOUNT ON

BEGIN TRY
    BEGIN TRANSACTION
        EXEC(@SQL)
    COMMIT TRANSACTION
END TRY

BEGIN CATCH
    DECLARE @ErrorMessage nvarchar(max), @ErrorSeverity int, @ErrorState int
    SELECT @ErrorMessage = N'Error Number: ' + CONVERT(nvarchar(5), ERROR_NUMBER()) + N'. ' + ERROR_MESSAGE() + ' Line ' + CONVERT(nvarchar(5), ERROR_LINE()), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE()
    ROLLBACK TRANSACTION
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
END CATCH

GO

-- Test it
EXEC usp_Execute_SQL_Within_Transaction @SQL = 'SELECT 1; SELECT 2'
EXEC usp_Execute_SQL_Within_Transaction @SQL = 'SELECT 1/0; SELECT 2'
EXEC usp_Execute_SQL_Within_Transaction @SQL = 'EXEC usp_Another_SP'

回答by Chuck Bevitt

The way to stop execution in a stored procedure after an error has occurred and bubble the error back to the calling program is to follow each statement that might throw an error with this code:

在发生错误后停止在存储过程中执行并将错误冒泡回调用程序的方法是使用以下代码跟踪可能引发错误的每个语句：

If @@ERROR > 0
Return

I was surprised myself to find out that execution in a stored procedure can continue after an error - not realizing this can lead to some hard to track down bugs.

我很惊讶地发现存储过程中的执行可以在出错后继续执行 - 没有意识到这会导致一些难以追踪的错误。

This type of error handling parallels (pre .Net) Visual Basic 6. Looking forward to the Throw command in SQL Server 2012.

这种类型的错误处理与（pre .Net）Visual Basic 6 类似。期待 SQL Server 2012 中的 Throw 命令。

回答by Yuri Makassiouk

Given that you haven't moved to 2012 yet, one way to implement the bubbling up of the original error code is to use the text message part of the exception you are (re)throwing from the catch block. Remember that it can contain some structure, for example, XML text for your caller code to parse in its catch block.

鉴于您还没有进入 2012 年，实现原始错误代码冒泡的一种方法是使用您从 catch 块（重新）抛出的异常的文本消息部分。请记住，它可以包含一些结构，例如，供调用方代码在其 catch 块中解析的 XML 文本。

回答by Piotr Rodak

From a design point of view, what is the point of throwing exceptions with original error numbers and custom messages? To some extent it breaks the interface contract between applications and the database. If you want to catch original errors and handle them in higher code, don't handle them in the database. Then when you catch an exception you can change the message presented to the user to anything you want. I would not do it though, because it makes your database code hmm 'not right'. As others said you should define a set of your own error codes (above 50000) and throw them instead. Then you can hanle integrity issues ('Duplicate values are not allowed') separately from potential business issues - 'Zip code is invalid', 'No rows were found matching the criteria' and so on.

从设计的角度来看，抛出带有原始错误号和自定义消息的异常有什么意义？在某种程度上，它打破了应用程序和数据库之间的接口契约。如果您想捕获原始错误并在更高的代码中处理它们，请不要在数据库中处理它们。然后，当您捕获异常时，您可以将呈现给用户的消息更改为您想要的任何内容。不过，我不会这样做，因为它会使您的数据库代码“不正确”。正如其他人所说，您应该定义一组自己的错误代码（50000 以上）并抛出它们。然后，您可以将完整性问题（“不允许重复值”）与潜在的业务问题分开处理——“邮政编码无效”、“找不到符合条件的行”等等。