How to return the actual error message from SQL RaiseError, instead of "Msg 50000"?

Question

Given the following stored procedure:

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

What I'm attempting to do is raise a custom error, and return that error when raised, but all that I get when I run the proc is:

Msg 50000, Level 16, State 1, Procedure MergeCustomers, Line 90

I'll be calling this proc from inside a C# Winform, and I'd like to actually have the true error message available, so I know what the heck it is, and can show it to the users. And I'd like to have it testable in the Proc as well.

I'm expecting, when I run this proc in SQL as

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

to see a message like:

Customers cannot be merged. These customers have potential duplicate subscriptions, and might need a refund to occur before merging. Please contact Support.

Answer 1

Remove + ERROR_MESSAGE() from the first SET statement. There is no error message at that point so the raised error message is 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

Answer 2

You can simply use a try catch block and catch Exception and use Message property of it. It contains the error message for you.

To test you can create this procedure:

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

Then test it this way:

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();
}