T-SQL 抛出异常 [英] T-SQL Throw Exception

问题描述

我在 T-SQL 存储过程中使用 THROW 语句时遇到了著名的语法错误".我已经谷歌搜索并检查了 StackOverflow 上的问题，但提出的解决方案(奇怪的是，被接受)对我不起作用.

I am facing the famous 'Incorrect syntax' while using a THROW statement in a T-SQL stored procedure. I have Googled it and checked the questions on StackOverflow but the solutions proposed (and strangely, accepted) do not work for me.

我正在修改一个存储过程，如下所示:

I am modifying a stored procedure as follows:

ALTER PROCEDURE [dbo].[CONVERT_Q_TO_O]
    @Q_ID int = NULL,
    @IDENTITY INT = NULL OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @EXISTING_RECORD_COUNT [int];

    SELECT
        @EXISTING_RECORD_COUNT = COUNT (*)
    FROM
        [dbo].[O]
    WHERE
        [Q_ID] = @Q_ID

    IF @EXISTING_RECORD_COUNT = 0
    BEGIN
        -- DO SOME STUFF HERE

        -- RETURN NEW ID
        SELECT @IDENTITY = SCOPE_IDENTITY()
    END
    ELSE
    BEGIN
         THROW 99001, 'O associated with the given Q Id already exists', 1;
    END
END
GO

当我编写这个 T-SQL 时，我收到一个错误提示

When I code this T-SQL I get an error saying

'THROW' 附近的错误陈述.期待对话、对话、分发或交易

Incorrect statement near 'THROW'. Expecting CONVERSATION, DIALOG, DISTRIBUTED, or TRANSACTION

所有解决方案都建议在THROW"之前或ELSE BEGIN"语句之后放置一个分号.当我修改 T-SQL 时，我只是收到Incorrect statement near 'THROW'"错误并且似乎找不到解决方案.

All solutions suggest to put a semi-colon either before 'THROW' or after 'ELSE BEGIN' statements. When I modify the T-SQL I simply get the "Incorrect statement near 'THROW'" error and can't seem to find a solution.

有什么建议吗?

推荐答案

对于 SQL Server 2012 或更高版本:

For SQL Server 2012 or later:

;THROW 60000, 'your message here', 1

如果您希望将变量传递给您的消息，请使用:

If you wish to pass a variable to your message use this:

DECLARE
    @Errors INT = 2,
    @ErrMsg NVARCHAR(500)

SET @ErrMsg = 'You have '+CAST(@Errors AS NVARCHAR) + ' errors!'
;THROW 60000, @ErrMsg, 1

请注意，与 RAISERROR 不同，THROW 会阻止进一步的代码执行.

THROW 文档

传统选项:

RAISERROR('your message here', 16, 1)

如果您希望将变量传递给您的消息，请使用:

If you wish to pass a variable to your message use this:

DECLARE
    @Errors INT = 2,
    @ErrMsg NVARCHAR(500)

SET @ErrMsg = 'You have '+CAST(@Errors AS NVARCHAR) + ' errors!'
RAISERROR(@ErrMsg, 16, 1)

查看sql server版本:SELECT @@VERSION

To check sql server version: SELECT @@VERSION

这篇关于T-SQL 抛出异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！