Unanswered

Successful call to a stored procedure in Canvas app doesn't return table in the Resultset

(0) Share

Report

Posted on by maxpower45255

223

I have a canvas app connecting to sql server. My proc returns data. It's a simple "select * from myTable" query and has nothing fancy about it. No temp tables, cursors, etc.

When in the the PowerFx window, trying to call the procedure, the Intellisense works until after the ".Resultsets" and then errors out when I add ".Table1". Do you know why a simple "select * from myTable" doesn't work?

I found a workaround (see article View results in SQL Server - Power Apps | Microsoft Learn). But I'm puzzled why I'd have to resort to this to get it to work. See the "Untyped Results" paragraph:
"Some complicated stored procedures return untyped results. This result is common for stored procedures that use temporary tables. Power Apps can't easily determine the results ahead of time. Therefore, the return is marked as untyped and you can't access these results directly. You must first provide a type."

==== WORKAROUND========

Set(
MyUntypedObject, // pull results into variable
MyDB.dboprocPowerAppDemo().ResultSets
);
Set(
table1, // put Table1 into table1
MyUntypedObject.Table1
);
Set(
ResultSet,
ForAll(
table1, // extract JSON from table1
{
OrderNumber: Text(ThisRecord.'Order Number'),
ItemNo: Text(ThisRecord.'Item No.'),
UnitOfMeasure: Text(ThisRecord.UnitofMeasure),
}
)
);

thanks!

Categories:

Power Apps Pro Dev & ISV

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

Pstork1 68,697 Most Valuable Professional on at

Like (1)

Report

In my experience every results set table that is returned by a stored procedure in Power Apps needs to be read using ParseJSON(). Since ParseJSON() returns untyped results you need to cast the columns returned to their respective types. Its one of the reasons I normally use Power Automate whenever calling a stored procedure since it returns typed results in most cases.

----------------------------------------------------------------------------------
If this Post helped you, please click "Does this answer your question" and give it a like to help others in the community find the answer too!

Paul Papanek Stork, MVP
Blog: https://www.dontpapanic.com/blog

Was this reply helpful? Yes No
maxpower45255 223 on at

Like (0)

Report

Thanks @Pstork1. I personally prefer the workaround over power automate flows since you can quickly glance at the powerFX code right inside the app. But the documentation clearly states that you can access the tables directly in powerFX (if the procedure doesn't use temporary tables, etc). I hope MSFT can fix this behavior.

RESULT SETS
Other tables can be accessed through their name, for example, Table1, Table2, or Table3.

<datasourceName>.<StoredprocedureName>({<paramName1: value, paramName2: value, ... >}).ResultSets.Table1

Was this reply helpful? Yes No
MB-23070938-0 48 on at

Like (0)

Report

Is your server on premises or Azure? on premises returns untyped object by default.

Was this reply helpful? Yes No
Suggested answer

Arun India 4 on at

Like (0)

Report

Untyped Object results can be collected by

ClearCollect(
Collectioname,
ForAll(
Table(dbName.sp_procedureName().ResultSets.Table1),
{id: Text(ThisRecord.Value.id),
permission: Text(ThisRecord.Value.Permission),
status: Text(ThisRecord.Value.status)
)
);

Was this reply helpful? Yes No