MSSQL-Plus - Appending the Query output to incoming payload.msg

[tusharacc]

I am building a simple workflow using Node-Red and Ms-SQL. The MS-SQL Node receives an dictionary object with multiple key-value. MS-SQL node is executing in query mode, and returns value from database. My requirement is - to append the value that is received from query to the incoming Payload.msg. I have modified my query to attain this -

Select  '{{{payload.ProductType}}}' As PolicyType,
'{{{payload.RuleType}}}' As RuleType,
'{{{payload.SystemAssignId}}}' As SystemAssignId,
'{{{payload.TransTypeCd}}}' As TransType,
'{{{payload.TransEffDt}}}' As TransEffDt,
'{{{payload.AddlRetPremAmt}}}' As AddlPrem,
'{{{payload.TermPremAmt}}}' As TermPrem,
'{{{payload.PolicyExpDt}}}' As PolicyExp,
'{{{payload.PolicyEffDt}}}' As PolicyEff,
a.PolicyLossInd,b.LossDt, b.LossPaidAmt  From [MyTableA] (nolock) a
left outer join  MyTableB] b on b.SystemAssignId = a.SystemAssignId and  b.TransSeqNo = a.TransSeqNo
Where a.SystemAssignId = '{{{payload.SystemAssignId}}}' and a.TransSeqNo = 1

This works well, however there is a slight problem. If the incoming message payload key:value changes, I need to update the query as well, which is bad. Hence I believe there might be a better way of handling this issue. Any suggestions/pointer

[Steve-Mcl]

So are you appending values from payload into the SQL query so that every row returned has these values?

If you simply want to merge the payload with the results rows you can do that after the SQL has executed.

[tusharacc]

@Steve-Mcl , Not sure if I understand your solution. I have found another solution. I have realized that msg object (if not destroyed) persists throughout the flow. So before calling database, I have a function call that will inject all the keys in msg.payload to msg.