Node-Red output return from MS SQL Server stored procedure

[AndyO]

Hi.
I try to resolve problem where i pass two objects to stored procedure in MS SQL Server:
This is function before MS SQL Server node

// Check if both weight and SSCC have been received
var weightReceived = flow.get("weightReceived");
var ssccReceived = flow.get("ssccReceived");

if (weightReceived && ssccReceived) {
    // Both weight and SSCC values are received
    var sscc = flow.get("ssccNumber");
    var weight = flow.get("weightValue");

    // Prepare the payload object for the MSSQL node
    msg.payload = {
        PalletSSCC: sscc,
        Weight: weight,
        Message: null // Initialize Message as null
    };

    // Reset flags
    flow.set("weightReceived", false);
    flow.set("ssccReceived", false);
} else {
    // If either weight or SSCC is missing, set msg.payload to null
    msg = null;
}

return msg;

Previously this stored procedure asked for two parameters - PalletSSCC and Weight and everything worked fine.

Now there is added return Message object

procedure [dbo].[PalletWeightSet](@PalletSSCC varchar(50), @Weight decimal(7, 3), @Message nvarchar(MAX) = null OUT)

What would be solution?
I tried to add output in MS SQL node but it always set type to number.

If i run query directly in SSMS then it works:

DECLARE @Message NVARCHAR(MAX)
EXEC [dbo].[PalletWeightSet] 
    @PalletSSCC = '01000000614410324005504515',
    @Weight = 100.5,  -- Replace with the actual weight
    @Message = @Message OUT

PRINT @Message

And it prints message from stored procedure.

[Steve-Mcl]

output parameters will be set in the msg.payload when you select the output type as "driver output"

---

Why, you might ask?

Well, mssql-plus can do many more things than the old MSSQL node but in order to be an in-place replacement, certain modes and compatibilities needed to be catered for.

In Original Output mode, you simply get "the data". But in order to get access to row counts, output parameters etc, messages, error codes, etc, etc, I needed a way to adjust what the node outputs. This was achieved by permitting either the Original (data only) OR the full driver (NODE-MSSQL) output.

[AndyO]

Thanks! I was on right track but one mistake with Error Handling - ''Send in msg.error'' was selected for me.
After changing to "Throw error" i did receive expected output.

Again! Thanks for fast response @Steve-Mcl