Answered

Model Driven Update Dataverse data SPROC return values

(0) Share

Report

Posted on by AJADS

I am currently scratching my head trying to work out how to save into the current DataVerse record values returned using Power Automate from an MS Store Procedure.

I have built a simple app that just passed 2 values into a SPROC and I wish to save the returned value(s) into the record.

I have added a Cloud Flow.

Starting Thus

I presumed that by mapping the Result to SP_NumbersADD ... taDa the data would be save in the current record that was updated. I accept that my delusions were misplaced.

Can someone please give me a pointer as to what Steps I need to take after this point to get the Result saved within the DataVerse record. Everything else work but no cigar.

Appreciate your help.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (3)

Answers (2)

Sort by

Linn Zaw Win 2,996 on at

Like (0)

Report
Copy link

Link copied!

Does that Stored Procedure update the values into the SQL table which is exposed to Dataverse as a virtual table?

If that's the case, you can query the updated data of the record from that virtual table using the List rows action with a filter or Get a row by ID action (if you have the ID of that record)

Was this reply helpful? Yes No
Verified answer

AJADS 23 on at

Like (0)

Report
Copy link

Link copied!

Hi Linn ... No my requirement is, to have a native DataVerse Model Driven Form that uses external Stored Procedures just to provide specific calculations that are required and return the values back to the form that triggered the Update/Insert Trigger.

We have an extensive CRM all built in Model Driven, all of our calculatory models are within our Data Warehouse (MS) so in order to be able to extend the CRM everything is being retained within the DataVerse environment but calling out to the SPROCs. Thus the issue with adding the return values from the SPROC into the DataVerse table.

I looked into using virtual tables but the loss of native functionality within Model Driven and other considerations made it not fit for the purpose.

Appreciate your help.

Was this reply helpful? Yes No
Verified answer

Linn Zaw Win 2,996 on at

Like (1)

Report
Copy link

Link copied!
@AJADS

Is your SQL server on-premises or cloud?

When using the Stored procedure on an on-premises SQL Server, these are the following limitations:

Output values for OUTPUT parameters are not returned. You can still specify input values for OUTPUT parameters.

Return value is not available.

Only the first result set is returned.

Dynamics schemas are not supported for result sets.

If it is a cloud SQL server, "Execute stored procedure (V2)" returns outputs of the operation as dynamic value and you can use the Update a row Dataverse action to update the value back. Can you check the OUTPUTs screen of the flow run?

I don't have a SQL server in my tenant to test but here are some useful links from the community who has done it

https://powerapps.microsoft.com/blog/return-an-array-from-a-sql-stored-procedure-to-powerapps-split-method/

https://powerusers.microsoft.com/t5/Building-Flows/Run-a-Stored-Procedure-and-Send-Emails/td-p/440155

https://powerusers.microsoft.com/t5/Building-Flows/Help-with-extracting-data-from-Execute-Stored-Procedure-V2/td-p/1537760

https://powerusers.microsoft.com/t5/Building-Power-Apps/How-to-get-single-value-from-stored-procedure-in-PowerApps/td-p/507332

https://powerusers.microsoft.com/t5/Building-Power-Apps/executing-a-stored-procedure-not-returning-value-from-proc-on/td-p/838066

Was this reply helpful? Yes No