cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Continued Contributor
Continued Contributor

I am trying to get a return value from an SQL Stored Procedure by the back door.

I am calling this flow from my app:

 

    ClearCollect(resultCollection,UpdateControlSheetSingle.Run(SelectedItem.ID));

 

The flow runs a Stored Procedure that returns a return value:

 

    SELECT 'Return_Value' = @return_value

 

I am seeing this data coming back in my Run History:

 

   

{
"OutputParameters": {},
"ResultSets": {
"Table1": [
{
"Return_Value": 1
}
]
}
}
 
I just do not seem to be able to see it in my app.
First I try to store it in a variable:
Set(gvDbResult,
First(resultCollection).'Return Value');
 
When I look this up under Collections, resultCollection is empty? gvDbResult has no value? It should be either a 1 or 0 (zero).
In the next statement I wanna do a notify:
 
If(gvDbResult = 1, Notify("Control Sheet updated.",NotificationType.Success);
 
What am I doing wrong?
 
here is all the code from that code block:
 

    UpdateContext({Updated: false});
    If(IsBlank(txtActualQtyDelivered.Text),
        Notify("Actual Qty Delivered is Blank"));
    Validate('[dbo].[TBL_JS_GOODS_IN_CONTROLSHEET]',"ActualQtyDelivered", Value(txtActualQtyDelivered.Text));
    If(!IsBlank(txtActualQtyDelivered.Text),

    Patch(
        '[dbo].[TBL_JS_GOODS_IN_CONTROLSHEET]',
        LookUp('[dbo].[TBL_JS_GOODS_IN_CONTROLSHEET]',ID = SelectedItem.ID),
        {
        ActualQtyDelivered:Value(txtActualQtyDelivered.Text),
        UpdatedBy:User().FullName,
        UpdatedOn: Now()
        });

    ClearCollect(resultCollection,UpdateControlSheetSingle.Run(SelectedItem.ID));

    Set(gvDbResult,
        First(resultCollection).'Return Value');

    UpdateContext({Updated: true});
    )

 

What am I doing wrong? Why is resultCollection empty?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver II
Resolver II

Re: I am trying to get a return value from an SQL Stored Procedure by the back door.

@stapes 

 

In your flow you need to specifically return the Table1 results. As the last step in your flow, use the response action and set the body to be the ResultSets  Table1. 

 

To generate the schema use the generate from sample by copying the output of the stored procedure step from a previous flow run and paste in as the sample.

 

Then your PowerApps code below for putting into a collection then reading the first record should work just fine. Let me know if you have further questions.

 

Hope this helps.

 

Mike

View solution in original post

1 REPLY 1
Highlighted
Resolver II
Resolver II

Re: I am trying to get a return value from an SQL Stored Procedure by the back door.

@stapes 

 

In your flow you need to specifically return the Table1 results. As the last step in your flow, use the response action and set the body to be the ResultSets  Table1. 

 

To generate the schema use the generate from sample by copying the output of the stored procedure step from a previous flow run and paste in as the sample.

 

Then your PowerApps code below for putting into a collection then reading the first record should work just fine. Let me know if you have further questions.

 

Hope this helps.

 

Mike

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (6,521)