Answered

Referencing output parameters from Stored Procedure Flow in PowerApps

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

The new Respond to PowerApps action in Flow looks to answer many prayers, but I can't get it to work with a SQL Server Stored Procedure. I'm trying to follow the Stephen Siciliano blog here.

I have a stored proc with several input parameters and 4 output parameters of various types (text, integer, bit). I create a new Flow. All the parameters, whether input or output, appear in the Execute Stored Procedure action. I ignore the output params and set the inputs to Ask in PowerApps dynamic content.

Then I add the Respond to PowerApps action. I select 'See more' by the Execute Stored Procedure option under Dynamic Content, but all I get is ReturnCode, not the output parameters. The flow executes ok but ReturnCode has no content.

Thinking perhaps the output parameter has to be singular and text, I've tried returning only one text parameter, but still don't see it.

I've tried the Request - Response action discussed here. This looks promising - it lists OutputParameters in the dynamic content options. Again the Flow runs but there is no content returned.

What am I doing wrong?

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Sort by

v-micsh-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
What is the output of the Stored Procedure within Microsoft Flow?

You should be able to get the procedure results with the following expression under Microsoft Flow:

body('<Action_name>')?['ResultSets']

Which you may reference the detailed value by adding:

body('<Action_name>')?['ResultSets']?['FieldName']

Regards,

Michael

Was this reply helpful? Yes No
JonathanGibbs 313 on at

Like
a
(1)

Report
Copy link

Link copied!

Thanks Michael
Here's a sample flow. The Stored Procedure 'kmJunk' takes one integer input parameter and returns a single integer output parameter with the value 55:

The flow runs successfully and here's the result:

Surely this should be showing an output parameter (pOutput, with a value of 55)? The Stored Procedure is being fired and working correctly, and if I run it in SSMS I get some expected output parameter.
Not sure where to start with this..!
thanks
Jonathan

Was this reply helpful? Yes No
Verified answer

jonathanO 98 on at

Like
a
(4)

Report
Copy link

Link copied!

Just in case anyone else sees this, Flow cannot return OUTPUT parameters from a stored procedure per the documentation:

https://docs.microsoft.com/en-us/connectors/sql/

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@v-micsh-msftHi Michael. Your response here is very helpful with learning how to access the values within the ResultsSet. Is there any documentation or tutorials that elaborates deeper on how to do this?

Thanks!
Sean

Was this reply helpful? Yes No
v-micsh-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Anonymous,

Sure. Microsoft Flow is built on top of Azure Logic Apps, so they would all work with the same language:

Workflow definition Language schema

Best regards,

Was this reply helpful? Yes No
johnnie on at

Like
a
(1)

Report
Copy link

Link copied!
Within the stored procedure, define the output parameter....
Within the Flow, define a Respond to a PowerApp or flow step. In my case the Output parameter is the second INT that I am returning...
Quick TIP. If the data type is an INT, you must cast it to an INT via a formula, such as ...

int(body('ExecuteStoredProcedureV2')?['outputparameters/NewRootChildID'])

Finally, within the PowerApp, capture the return record from the call to the Flow, and reference the output value from the Stored Procedure.

UpdateContext({ReturnValuesVar: CopyPlantHierarchyTree.Run(ThisItem.ChildId, ThisItem.ParentId)}); If (ReturnValuesVar.returncodevalue = 0, UpdateContext({OutputValue: ReturnValuesVar.newrootchildid}); ...

Here is the output from the flow after it was called from PowerApps...

Was this reply helpful? Yes No
MarrinerDev on at

Like
a
(0)

Report
Copy link

Link copied!
Note that in this documentation, it only refers to on-premises SQL Server having this limitation. As per Johnnie and Michael's reply, it does work for Azure SQL.

3) When invoking a Stored Procedure on an on-premises SQL Server, we have the following limitations:
Output values for OUTPUT parameters are not returned. You can still specify input values for OUTPUT parameters.

Was this reply helpful? Yes No