How to get the custom error of a stored procedure which is executed inside a stored procedure?

Question

I am currently studying how to handle exceptions from a nested stored procedure in an SQL server.

Unfortunately, a scenario came across where I want to output the custom error on a stored procedure[2] which is executed inside a stored procedure[1].

Here is an example:

/*
    create the master-table
*/
CREATE TABLE master_table
(
    pk_id INT IDENTITY(5,5),
    access_num INT UNIQUE NOT NULL,
    first_name NVARCHAR(MAX)
    PRIMARY KEY (pk_id)
)
GO

/*
    create the child-table
*/
CREATE TABLE child_table
(
    pk_id INT IDENTITY(1,1),
    val INT UNIQUE,
    msgs NVARCHAR(MAX) NOT NULL
    PRIMARY KEY (pk_id)
)
GO


/*
    create the master procedure
*/
CREATE or ALTER PROC uspInsertIntoMaster
    @AccessNum INT,
    @FirstName NVARCHAR(MAX),
    @Message NVARCHAR(MAX)
AS
BEGIN
    DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
    SET XACT_ABORT, NOCOUNT ON;

    BEGIN TRY
        BEGIN TRAN
            INSERT INTO master_table (access_num, first_name)
            VALUES (@AccessNum, @FirstName)

            EXEC uspInsertIntoChild 4, @Message;

        IF (@@TRANCOUNT = 1)
            COMMIT
    END TRY

    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK;

        THROW;

        IF (XACT_STATE()) = -1
                BEGIN
                    ROLLBACK TRANSACTION;
                END;

        IF (XACT_STATE()) = 1
            BEGIN
                COMMIT TRANSACTION;
            END;
    END CATCH
END
GO


/*
    create the child procedure
*/
CREATE or ALTER PROC uspInsertIntoChild
    @Value INT,
    @Message NVARCHAR(MAX)
AS
    DECLARE @ErrorMessage NVARCHAR(2048);
BEGIN
    DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
    SET XACT_ABORT, NOCOUNT ON;

    BEGIN TRY
        IF EXISTS(SELECT 1 FROM child_table WHERE val = @Value)
            BEGIN
                SET @ErrorMessage = FORMATMESSAGE(50010, @Value);
                THROW 50010, @ErrorMessage, 1;
            END;

        ELSE
            BEGIN TRAN 
                INSERT INTO child_table (val, msgs) VALUES (@Value, @Message)

            IF (@@TRANCOUNT > 0)
                COMMIT
    END TRY

    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK;

        THROW;

        IF (XACT_STATE()) = -1
                BEGIN
                    ROLLBACK TRANSACTION;
                END;

        IF (XACT_STATE()) = 1
            BEGIN
                COMMIT TRANSACTION;
            END;
    END CATCH
END
GO

---

Custom ERROR Message

EXEC sys.sp_addmessage 
    @msgnum = 50010, 
    @severity = 16, 
    @msgtext = N'Invalid Input. Error occurred at procedure uspInsertIntoChild',
    @lang = 'us_english';   
GO

If I try to execute the below code. I expect it to fail on the second execution and throw the custom error above.

EXEC uspInsertIntoMaster 4, N'miKeL', N'Get Well Soon'
EXEC uspInsertIntoMaster 5, N'miKeL', N'Get Well Soon'

But unfortunately, instead of having the custom error I've got

Msg 50010, Level 16, State 1, Procedure uspInsertIntoChild, Line 14 [Batch Start Line 105] Error: 50010, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

---

EDIT (@Charlieface)

First, I removed the TRY/CATCH block from master proc first and then both, yet still the same error occur. I also removed the DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS as @M.Ali suggest.

new Master Proc

CREATE or ALTER PROC uspInsertIntoMaster
    @AccessNum INT,
    @FirstName NVARCHAR(30),
    @Message NVARCHAR(MAX)
AS
BEGIN
    SET XACT_ABORT, NOCOUNT ON;
    
    BEGIN TRANSACTION
        INSERT INTO master_table (access_num, first_name)
        VALUES (@AccessNum, @FirstName)

        EXEC uspInsertIntoChild 4, @Message;

    IF (@@TRANCOUNT = 1)
        COMMIT TRANSACTION
END
GO

new Child Proc

CREATE or ALTER PROC uspInsertIntoChild
    @Value INT,
    @Message NVARCHAR(MAX)
AS
    DECLARE @ErrorMessage NVARCHAR(MAX);
BEGIN
    
    SET XACT_ABORT, NOCOUNT ON;
    IF EXISTS(SELECT 1 FROM child_table WHERE val = @Value)
        BEGIN
            SET @ErrorMessage = FORMATMESSAGE(50010, @Value);
            -- I want to thow
            THROW 50010, @ErrorMessage, 1;
        END;

    ELSE
        BEGIN TRANSACTION
            INSERT INTO child_table (val, msgs) VALUES (@Value, @Message)

        IF (@@TRANCOUNT > 0)
            COMMIT TRANSACTION
END
GO

EDIT 2

Silly me. I edited the formatted messages. But never thought of editing also the Control Character %s which indicates for string character. I tried to pass an integer parameter to the error message, so it should be %i.

final FORMATTED Error Message

EXEC sys.sp_addmessage 
    @msgnum = 50010, 
    @severity = 16, 
    @msgtext =
    N'Invalid Input %i. Error occured at procedure uspInsertIntoChild', /*Mobile number [%s] is invalid. Input a valid number*/
    @lang = 'us_english';   
GO

Answer 1

The message you are using does not have any formatting paramters, so FORMATMESSAGE is throwing an error.

You need something like this:

EXEC sys.sp_addmessage 
    @msgnum = 50010, 
    @severity = 16, 
    @msgtext = N'Invalid Input %s. Error occurred at procedure uspInsertIntoChild',
    @lang = 'us_english';   

---

By the way, if you use XACT_ABORT ON then it is not necessary to catch and rollback. It is only necessary if you are using custom logging, which in this case you are not.

Futhermore, the code you have currently doesn't work for other reasons:

• In the CATCH you would want to ROLLBACK never COMMIT usually.
• If XACT_STATE = -1 then the ROLLBACK will fail
• No code is executed after THROW; anyway.

So I would say remove the whole TRY/CATCH, keep XACT_ABORT ON and log messages as you have done using sp_addmessage