# Filter a SQL query with variables from a previous one in Node Red

**URL:** <https://discourse.nodered.org/t/filter-a-sql-query-with-variables-from-a-previous-one-in-node-red/68025>\
**Category:** General\
**Created:** [21 September 2022 10:31 UTC](https://discourse.nodered.org/t/filter-a-sql-query-with-variables-from-a-previous-one-in-node-red/68025 "2022-09-21T10:31:43Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![SigmaMan](https://avatars.discourse-cdn.com/v4/letter/s/838e76/32.png) [@SigmaMan](https://discourse.nodered.org/u/SigmaMan)\
**Post date:** [21 September 2022 10:31 UTC](https://discourse.nodered.org/t/filter-a-sql-query-with-variables-from-a-previous-one-in-node-red/68025/1 "2022-09-21T10:31:44Z")

</div>

Hello all,

I try to query one SQL database in a Node Red flow to extract a few fields and declare them as variables, then re-use them to filter a second SQL query from another database.

I want to put the two msSQL nodes consecutively, without using flow variables.

example:  
1st query:  
"DECLARE @N\_1Shift VARCHAR;   
SELECT TOP (1)   
@N\_1Shift = [ShiftNo]  
FROM db1"

2nd query:  
"DECLARE @N\_1Shift VARCHAR;  
SET @N\_1Shift = '{{msg.payload.N\_1Shift}}'  
SELECT TOP(1)  
[ShiftStart]  
FROM db2  
WHERE [ShiftNo] = @N\_1Shift"

Do you know what is not working here? I tried both '{{ }}' and '{{{ }}}'

 ![pb sql nodered](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/7/97b33f64b6e4113ec104fbf8f286469ae9d5fd4b.png)

---

<div class="post-metadata">

**Author:** ![SigmaMan](https://avatars.discourse-cdn.com/v4/letter/s/838e76/32.png) [@SigmaMan](https://discourse.nodered.org/u/SigmaMan)\
**Post date:** [29 September 2022 08:52 UTC](https://discourse.nodered.org/t/filter-a-sql-query-with-variables-from-a-previous-one-in-node-red/68025/2 "2022-09-29T08:52:22Z")

</div>

Anyone know how to do this?

---

<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:** [29 September 2022 10:21 UTC](https://discourse.nodered.org/t/filter-a-sql-query-with-variables-from-a-previous-one-in-node-red/68025/3 "2022-09-29T10:21:36Z")

</div>

which MSSQL node are you using? node-red-contrib-mssql-plus?

can you share your flow? (Select the 5 nodes and use `CTRL+E` to export`, paste using the forum toolbar code `\</\>` button)

can you capture data from the 1st MSSQL node and paste it into a reply so I can try to emulate it?

---

<div class="post-metadata">

**Author:** ![SigmaMan](https://avatars.discourse-cdn.com/v4/letter/s/838e76/32.png) [@SigmaMan](https://discourse.nodered.org/u/SigmaMan)\
**Post date:** [29 September 2022 11:00 UTC](https://discourse.nodered.org/t/filter-a-sql-query-with-variables-from-a-previous-one-in-node-red/68025/4 "2022-09-29T11:00:10Z")

</div>

I'm using " node-red-contrib-mssql "

```auto
[{"id":"1e7f608b.990d6f","type":"inject","z":"a095fc4.58a32","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":460,"y":1600,"wires":[["caf57e0a.f4034"]]},{"id":"caf57e0a.f4034","type":"MSSQL","z":"a095fc4.58a32","mssqlCN":"d5935af4.d78498","name":"db1 Shifts","query":"/* QUERY0: Definition of previous shift bounds, with UTC correction */\t\t\nDECLARE @N_1Shift INT\t\t\nDECLARE @ShiftStart INT\t\t\nDECLARE @ShiftEnd INT;\t\t\n\t\t\nSELECT TOP (1)\t\t\n\t\t\n\t@N_1Shift = z.[ShiftSeq]\t\n\t,@ShiftStart = z.[tShiftStart] + z.[TimezoneGap]\t\n\t,@ShiftEnd = z.[tShiftEnd] + z.[TimezoneGap]\t\n\t\t\nFROM\t\t\n(\t\t\nSELECT TOP(1000)\t\t\n\t\t\n\t[ShiftSeq]\t\n\t,[tShiftStart]\t\n\t,[tShiftEnd]\t\n\t,DENSE_RANK() OVER (ORDER BY [ShiftSeq] DESC ) AS [RANK_SHIFT]\t\n\t,ROUND( ( CONVERT( FLOAT, GETUTCDATE() ) - CONVERT( FLOAT, GETDATE() ) ) * 86400 , 0) AS [TimezoneGap]\t\n\t\t\nFROM db1\t\t\nWHERE DATEADD(S, [tShiftEnd], '1970-01-01') > GETDATE() - 2\t\t\n\t\t\n) z\t\t\n\t\t\nWHERE z.RANK_SHIFT = 2;\t\t\n\nSELECT\n\t@N_1Shift AS [N_1_Shift]\n\t,DATEADD(S, @ShiftStart, '1970-01-01') AT TIME ZONE 'CENTRAL EUROPEAN STANDARD TIME' AS [SStart]\n\t,DATEADD(S, @ShiftEnd, '1970-01-01') AT TIME ZONE 'CENTRAL EUROPEAN STANDARD TIME' AS [SEnd]","outField":"payload","x":610,"y":1600,"wires":[["ba769cde.3f59b"]]},{"id":"ba769cde.3f59b","type":"MSSQL","z":"a095fc4.58a32","mssqlCN":"3a3d660b.4b118a","name":"db2 Data","query":"msg.shifts = msg.payload\n\nDECLARE @N_1Shift VARCHAR\t\t\nDECLARE @ShiftStart DATETIME\t\t\nDECLARE @ShiftEnd DATETIME;\n\nSET @N_1Shift = '{{msg.shifts.N_1_Shift}}'\nSET @ShiftStart = '{{flow.shiftvar.SStart}}'\nSET @ShiftEnd = '{{flow.shiftvar.SEnd}}';\n\nSELECT\n [dateHeure]\n\t,[num_balancelles]\n\t,[numTransac]\n\t,@N_1Shift AS [ShiftSeq]\nFROM db2\n\nWHERE [dateHeure] BETWEEN @ShiftStart AND @ShiftEnd\n\nORDER BY [dateHeure] DESC","outField":"payload","x":760,"y":1600,"wires":[["c2b0b364.a423"]]},{"id":"c2b0b364.a423","type":"debug","z":"a095fc4.58a32","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":910,"y":1600,"wires":[]},{"id":"d5935af4.d78498","type":"MSSQL-CN","z":"","name":"Guichen_MES","server":"SGUIWP0370.ad.ponet\\INSTPROD01","encyption":true,"database":"mattec_prohelp"},{"id":"3a3d660b.4b118a","type":"MSSQL-CN","z":"","name":"Guichen_SQP","server":"SGUIWP0370.ad.ponet\\INSTPROD01","encyption":true,"database":"sqpdb"}]

```

Data from the first query looks like this:

| N\_1\_Shift | SStart | SEnd |
| --- | --- | --- |
| 202209282 | 2022-09-28 19:10:00.000 +02:00 | 2022-09-29 03:00:00.000 +02:00 |

---

<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:** [29 September 2022 11:35 UTC](https://discourse.nodered.org/t/filter-a-sql-query-with-variables-from-a-previous-one-in-node-red/68025/5 "2022-09-29T11:35:56Z")

</div>

> [@SigmaMan](#):
>
> " node-red-contrib-mssql "

this node is very old, and has many known issues and more importantly doesnt support parameters which is ideal for what you are attempting (and also removes the chances of SQL injection hacks)

For example, using MSSQL-PLUS...

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

> [@SigmaMan](#):
>
> Data from the first query looks like this:
> 
> | N\_1\_Shift | SStart | SEnd |
> | --- | --- | --- |
> | 202209282 | 2022-09-28 19:10:00.000 +02:00 | 2022-09-29 03:00:00.000 +02:00 |

I cannot use a screenshot to simulate so when I said...

> [@Steve-Mcl](#):
>
> can you capture data from the 1st MSSQL node and paste it into a reply so I can try to emulate it

I meant use the "copy value" button and paste as text (usable JSON)

---

<div class="post-metadata">

**Author:** ![SigmaMan](https://avatars.discourse-cdn.com/v4/letter/s/838e76/32.png) [@SigmaMan](https://discourse.nodered.org/u/SigmaMan)\
**Post date:** [29 September 2022 12:21 UTC](https://discourse.nodered.org/t/filter-a-sql-query-with-variables-from-a-previous-one-in-node-red/68025/6 "2022-09-29T12:21:14Z")

</div>

My bad, here are the values:

```auto
[{"N_1_Shift":202209290,"SStart":"2022-09-29T01:00:00.000Z","SEnd":"2022-09-29T09:00:00.000Z"}]

```

I will check with my IT to see if we can implement mssql-plus. Thank you for your answer

---

<div class="post-metadata">

**Author:** ![SigmaMan](https://avatars.discourse-cdn.com/v4/letter/s/838e76/32.png) [@SigmaMan](https://discourse.nodered.org/u/SigmaMan)\
**Post date:** [6 October 2022 09:43 UTC](https://discourse.nodered.org/t/filter-a-sql-query-with-variables-from-a-previous-one-in-node-red/68025/7 "2022-10-06T09:43:49Z")

</div>

Hello,  
I have tries with MSSQL-PLUS, and find this:  
Either the second nodes finds no declared value, or I declare it in SQL, and then receive the field, but null.  
 ![2022 10 06 11 43](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/9/39223cb3ffde1f012003168c993a5b2e0d005956.png)

```auto
[{"id":"f796c5bdf579f511","type":"inject","z":"614035ec92d63219","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":200,"y":1440,"wires":[["f3cff0b8319b8a00"]]},{"id":"f3cff0b8319b8a00","type":"MSSQL","z":"614035ec92d63219","mssqlCN":"","name":"db1 shifts","outField":"payload","returnType":"1","throwErrors":1,"query":"SELECT TOP (1)\t\t\r\n\r\n\t[ShiftSeq] AS [N_1_Shift]\r\n\r\nFROM\t\t\r\n(\t\t\r\nSELECT TOP(1000)\t\t\r\n\t\t\r\n\t[ShiftSeq]\t\r\n\t,[tShiftStart]\t\r\n\t,[tShiftEnd]\t\r\n\t,DENSE_RANK() OVER (ORDER BY [ShiftSeq] DESC ) AS [RANK_SHIFT]\t\r\n\t,ROUND( ( CONVERT( FLOAT, GETUTCDATE() ) - CONVERT( FLOAT, GETDATE() ) ) * 86400 , 0) AS [TimezoneGap]\t\r\n\t\t\r\nFROM db1\t\t\r\nWHERE DATEADD(S, [tShiftEnd], '1970-01-01') > GETDATE() - 2\t\t\r\n\t\t\r\n) z\t\t\r\n\t\t\r\nWHERE z.RANK_SHIFT = 2;","modeOpt":"queryMode","modeOptType":"query","queryOpt":"payload","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","rows":"rows","rowsType":"msg","params":[],"x":340,"y":1440,"wires":[["f96ff984f1288358"]]},{"id":"f96ff984f1288358","type":"MSSQL","z":"614035ec92d63219","mssqlCN":"","name":"db2 Data","outField":"payload2","returnType":"1","throwErrors":"0","query":"--DECLARE @N_1Shift INT;\n\nSELECT\n\t@N_1Shift * 2 AS [ShiftSeq]","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","rows":"","rowsType":"msg","params":[{"output":false,"name":"@N_1Shift","type":"int","valueType":"msg","value":"payload.N_1_Shift","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":480,"y":1440,"wires":[["44dd311515c73c82"]]},{"id":"44dd311515c73c82","type":"debug","z":"614035ec92d63219","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload2","targetType":"msg","statusVal":"","statusType":"auto","x":640,"y":1440,"wires":[]}]

```

---

<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:** [6 October 2022 10:00 UTC](https://discourse.nodered.org/t/filter-a-sql-query-with-variables-from-a-previous-one-in-node-red/68025/8 "2022-10-06T10:00:21Z")

</div>

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/5/2577560533dff3c2286e09c75e8e2ad65ceda3ae.png)

Does `msg.payload.N_1_Shift` even contain a value (my money is on NO!)

TIP: use debug node to check what comes out of `db1 shifts`

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/a/ba051a12a776dec78c5540ef76b1b1d24bf36a3f.png)

TIP2: Since your 2nd query is `SELECT	@N_1Shift * 2 AS [ShiftSeq]` there is no point in even asking the database to do this. Just set your first query to select `[ShiftSeq] * 2 as [ShiftSeq]`

* * *

### Other tip

There’s a great page in the docs ([Working with messages : Node-RED](https://nodered.org/docs/user-guide/messages)) that will explain how to use the debug panel to find the right path to any data item.

Pay particular attention to the part about the buttons that appear under your mouse pointer when you over hover a debug message property in the sidebar.

![BX00Cy7yHi](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/d/bd1f333a9e043b061b39917c328b0094d3e52d30.gif)

---

<div class="post-metadata">

**Author:** ![SigmaMan](https://avatars.discourse-cdn.com/v4/letter/s/838e76/32.png) [@SigmaMan](https://discourse.nodered.org/u/SigmaMan)\
**Post date:** [6 October 2022 10:46 UTC](https://discourse.nodered.org/t/filter-a-sql-query-with-variables-from-a-previous-one-in-node-red/68025/9 "2022-10-06T10:46:16Z")

</div>

I do get data from the "db1 shifts" node, when looking at the whole payload, but not with payload.N\_1\_Shift:  
 ![2022 10 06 12 43 _ 2](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/d/2dd27b60ada9ff867c5a9f836c16ac5175e0df4e.png)  
 ![2022 10 06 12 43 _ 1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/d/7d02250fea90916c2107b95a576e499d3da9e334.png)

As for the second tip, it doesn't seem to change anything, and I want to keep the "SELECT" syntax for when I reintroduce my 2nd SQL query properly.

---

<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:** [6 October 2022 10:49 UTC](https://discourse.nodered.org/t/filter-a-sql-query-with-variables-from-a-previous-one-in-node-red/68025/10 "2022-10-06T10:49:36Z")

</div>

> [@SigmaMan](#):
>
> I do get data from the "db1 shifts" node, when looking at the whole payload, but not with payload.N\_1\_Shift:

Yep, I know. And as I said...

> [@Steve-Mcl](#):
>
> Pay particular attention to the part about the buttons that appear under your mouse pointer when you over hover a debug message property in the sidebar.
> 
> ![BX00Cy7yHi](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/d/bd1f333a9e043b061b39917c328b0094d3e52d30.gif)

In other words, it is NOT `msg.payload.N_1_Shift`  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/3/c3a1f5d3c614ed9c54618b5040f8452ae5e83739.png)

Then use that in the next MSSQL node

---

<div class="post-metadata">

**Author:** ![SigmaMan](https://avatars.discourse-cdn.com/v4/letter/s/838e76/32.png) [@SigmaMan](https://discourse.nodered.org/u/SigmaMan)\
**Post date:** [6 October 2022 11:17 UTC](https://discourse.nodered.org/t/filter-a-sql-query-with-variables-from-a-previous-one-in-node-red/68025/11 "2022-10-06T11:17:58Z")

</div>

My bad, I didn't read your reply properly.  
The payload was "payload.recordset[0].N\_1\_Shift" and now everything works as intended 🙂.

Thank you very much for your support!

---

<div class="post-metadata">

**Author:** ![SigmaMan](https://avatars.discourse-cdn.com/v4/letter/s/838e76/32.png) [@SigmaMan](https://discourse.nodered.org/u/SigmaMan)\
**Post date:** [6 October 2022 11:23 UTC](https://discourse.nodered.org/t/filter-a-sql-query-with-variables-from-a-previous-one-in-node-red/68025/12 "2022-10-06T11:23:48Z")

</div>

For anyone interested, another mistake I made was using "@" in the variable name in the MSSQL-PLUS nodes Parameters Editor, which was unneccessary and caused the variable to be unrecognized.

![2022 10 06 13 23](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/5/a59d35850efc5a0a6ed7ba8581ec3c851977ed2b.png)

---

<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:** [20 October 2022 11:24 UTC](https://discourse.nodered.org/t/filter-a-sql-query-with-variables-from-a-previous-one-in-node-red/68025/13 "2022-10-20T11:24:15Z")

</div>

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