# Node-Red output return from MS SQL Server stored procedure

**URL:** <https://discourse.nodered.org/t/node-red-output-return-from-ms-sql-server-stored-procedure/86429>\
**Category:** General\
**Tags:** database\
**Created:** [14 March 2024 15:29 UTC](https://discourse.nodered.org/t/node-red-output-return-from-ms-sql-server-stored-procedure/86429 "2024-03-14T15:29:13Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![AndyO](https://avatars.discourse-cdn.com/v4/letter/a/77aa72/32.png) [@AndyO](https://discourse.nodered.org/u/AndyO)\
**Post date:** [14 March 2024 15:29 UTC](https://discourse.nodered.org/t/node-red-output-return-from-ms-sql-server-stored-procedure/86429/1 "2024-03-14T15:29:14Z")

</div>

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

```auto
// 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.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/9/d9e716cfc5a94676039c3aa0881a29fd5aa5d6a1.png)

Now there is added return Message object

```auto
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:

```auto
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.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [14 March 2024 16:49 UTC](https://discourse.nodered.org/t/node-red-output-return-from-ms-sql-server-stored-procedure/86429/2 "2024-03-14T16:49:35Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/7/274620bf467a1163fcffa5496a722a089fd3ebaf.png)

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/9/c9e23f8df85529139a3e9b4ac977b9bcb3ee1678.png)

* * *

### 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.

---

<div class="post-metadata">

**Author:** ![AndyO](https://avatars.discourse-cdn.com/v4/letter/a/77aa72/32.png) [@AndyO](https://discourse.nodered.org/u/AndyO)\
**Post date:** [14 March 2024 17:20 UTC](https://discourse.nodered.org/t/node-red-output-return-from-ms-sql-server-stored-procedure/86429/3 "2024-03-14T17:20:44Z")

</div>

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.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/9/f9bbbcd1d3b6d1a88b4222a602f42cf06cd25db5.png)

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/6/869eb828d100d22cc9ca912430858128f3cf37e0.png)

Again! Thanks for fast response @Steve-Mcl

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [28 March 2024 17:21 UTC](https://discourse.nodered.org/t/node-red-output-return-from-ms-sql-server-stored-procedure/86429/4 "2024-03-28T17:21:30Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.