如何从SQL RaiseError返回实际的错误消息，而不是“Msg 50000”

本文关键字：50000 Msg 消息 错误 SQL RaiseError 返回 | 更新日期: 2023-09-27 18:17:29

给定以下存储过程:

CREATE PROCEDURE [dbo].[MergeCustomers]
    @CustomerToKeep UNIQUEIDENTIFIER
  , @CustomerToDelete UNIQUEIDENTIFIER
AS 
    DECLARE @ERROR_MSG NVARCHAR(MAX)
      , @SEVERITY INT
      , @STATE INT
      , @CustomError NVARCHAR(MAX);
    BEGIN TRY
        BEGIN TRANSACTION;
        BEGIN
            SET NOCOUNT ON;
            BEGIN
                /* 1. Check for potential Duplicate Subscriptions if they exist, rollback. */
                IF EXISTS ( SELECT  [S].[SubscriptionType]
                            FROM    [dbo].[subscribers] AS [S]
                            WHERE   ( [S].[UserID] = @CustomerToKeep )
                                    OR ( [S].[UserID] = @CustomerToDelete )
                            GROUP BY [S].[SubscriptionType]
                            HAVING  COUNT([S].[SubscriptionType]) > 1 )
                    BEGIN
                        SET  @CustomError = N'Customers cannot be merged. These customers have potential duplicate'
                                + 'subscriptions, and might need a refund to occur before merging. '
                                + 'Please contact Support.' + ERROR_MESSAGE(); 
                        RAISERROR(@CustomError, 16, 1) --change to > 10
                    END 
            END                
        END
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        DECLARE @SQLErrorMessage NVARCHAR(2048);
        SET @SQLErrorMessage = ERROR_MESSAGE();
        RAISERROR (@SQLErrorMessage, 16, 1);
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
    END CATCH
GO

我试图做的是引发一个自定义错误，并在引发时返回该错误，但当我运行该进程时，我得到的全部是:

Msg 50000，级别16，状态1，过程MergeCustomers，第90行

我将从c# Winform内部调用这个过程，我想实际上有真正的错误信息可用，所以我知道它是什么，并可以显示给用户。我还想让它在程序中也可测试。

我期待，当我在SQL中运行这个过程

EXECUTE [dbo].[MergeCustomers] @CustomerToKeep = '6B88274A-38F0-11D5-9D8A-00A0C9D7DEE4', -- uniqueidentifier
    @CustomerToDelete = '06AB5121-A87D-11D5-9D9D-00A0C9D7DEE4' -- uniqueidentifier

查看如下消息:

不能合并客户。这些客户可能有重复的订阅，并且可能需要在合并之前进行退款。请联系技术支持。

如何从SQL RaiseError返回实际的错误消息，而不是“Msg 50000”

从第一个SET语句中删除+ ERROR_MESSAGE()。此时没有错误消息，因此抛出的错误消息是NULL。

CREATE PROCEDURE [dbo].[MergeCustomers]
    @CustomerToKeep uniqueidentifier
  , @CustomerToDelete uniqueidentifier
AS
    DECLARE @ERROR_MSG nvarchar(MAX)
      , @SEVERITY int
      , @STATE int
      , @CustomError nvarchar(MAX);
    BEGIN TRY
        BEGIN TRANSACTION;
        SET NOCOUNT ON;
        IF EXISTS ( SELECT  [S].[SubscriptionType]
                    FROM    [dbo].[subscribers] AS [S]
                    WHERE   ( [S].[UserID] = @CustomerToKeep )
                            OR ( [S].[UserID] = @CustomerToDelete )
                    GROUP BY [S].[SubscriptionType]
                    HAVING  COUNT([S].[SubscriptionType]) > 1 )
            BEGIN
                SET @CustomError = N'Customers cannot be merged. These customers have potential duplicate'
                    + 'subscriptions, and might need a refund to occur before merging. '
                    + 'Please contact Support.'; 
                RAISERROR(@CustomError, 16, 1); --change to > 10
            END;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        DECLARE @SQLErrorMessage nvarchar(2048);
        SET @SQLErrorMessage = ERROR_MESSAGE();
        RAISERROR (@SQLErrorMessage, 16, 1);
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
    END CATCH;
GO

您可以简单地使用try catch块捕获Exception并使用它的Message属性。它为您包含了错误信息。

要进行测试，您可以创建以下过程:

Create PROCEDURE [dbo].[TestProcedure]
AS
RAISERROR(N'Some error', 16, 1)
RETURN 0

然后这样测试:

var connection = new SqlConnection(@"Your connection string");
var command = new SqlCommand("dbo.TestProcedure", connection);
command.CommandType = CommandType.StoredProcedure;
try
{
    connection.Open();
    command.ExecuteNonQuery();
    connection.Close();
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}
finally
{
    if (connection.State == ConnectionState.Open)
        connection.Close();
}
相关文章：