Calling a SQL Stored Procedure directly from Power Apps does not include any Table items inside the ResultSets property

[KevinAlc0r]

First, I followed the procedure shown here in order to connect my SQL Stored Procedure to my canvas app. I was able to connect the stored procedure into my Power Apps. When I tried to call the stored procedure from inside my canvas app, the intellisense was able to detect the name of my stored procedure, the stored procedure itself returns ResultSets which according to the Microsoft page would contain a set of resulting tables. However in my case, the ResultSets contain nothing:

My stored procedure is a simple Select query that joins and sorts some tables into one table that shows a list of countries. It does not have any input arguments, it is supposed to return a result table using the Select query. I have already tested the query in SSMS and it worked fine. The stored procedure looks like this:

CREATE PROCEDURE sp_APCOUNTRYVIEW_SORTBY_LATEST_MODIFIEDDATE_2
AS
BEGIN
	SELECT apcView.CountryID, apcView.CountryName as 'Country Name', apcView.Capital, apcView.Language, apcView.Population, apcView.GDP, apcView.Currency, apcView.NaturalResource as 'Natural Resource', apcView.History, apcView.OfficeAddress as 'Office Address', apcView.POCFullName as 'POC', apcView.CompanyCompetition as 'Company Competition', apcView.Tax, apcView.CPI, apcView.Politics, apcView.EconomicIndicators as 'Economic Indicators', apcView.ProcurementRisks as 'Procurement Risks', apcView.Notes, apclog.ActionTypeID, apclog.ModifiedAt, apclog.ModifiedBy
	FROM vw_APCOUNTRY_TableView apcView
	INNER JOIN AP_COUNTRY_LOG apclog ON apcView.CountryID = apclog.CountryID
	INNER JOIN (
		SELECT CountryId, Max(ModifiedAt) as LatestDate
		FROM AP_COUNTRY_LOG 
		GROUP BY CountryId
		) grouped_apclog ON grouped_apclog.CountryID = apclog.CountryID AND grouped_apclog.LatestDate = apclog.ModifiedAt
	ORDER BY grouped_apclog.LatestDate DESC
END

Is there anything wrong with what I have been doing so far? Is it because this feature is still a preview feature? If this is indeed an issue/bug, what are some alternatives to this? Is calling the stored procedure from Power Automate (the old method) the only way?

Thanks a lot!

[VishalJhaveri]

This seems to be not possible. Please mark my answer as solution if it helps you. 

[iAm_ManCat]

Have you tried setting it to a variable first? You should be able to dig further into the ResultSets from there to determine how you are going to use it.

I would try this first, then use the data captured in the variable to determine what you are working with as it looks like the name in the next layer of the data might not be valid:

Set(
   ReturnedResultSets,
   APR_CORP_1.dbospAPCOUNTRYVIEWSORTBYLATESTMODIFIEDDATE2().ResultSets
)

Could you give that a try please?

[KevinAlc0r]

Hi!

I gave it a try just now and it doesn't seem to work either. I tried setting the variable to the ResultSets following your suggestion to the OnVisible property of my page and accessed the variable in the items property of a Modern Table component but there's still nothing that I can access from the variable

As you can see from the attached images, the variable that is set to the ResultSets itself is of the UntypedObject type which does not contain any Record objects inside it. Normally, if the ResultSets is an array/collection object, it should be of type Table per my understanding.

What do you think? Is this preview feature currently limited for use in Power Apps? Thanks a lot

[KevinAlc0r]

Thanks for your reply!

If you know why, could you share with me the reason of Power Apps inability to do this? Based on the Microsoft page I have linked at the beginning of the page, this shouldn't have been an issue

[iAm_ManCat]

Hmmm, so according to the docs your OnVisible should be:

Set(
   APCountryTableResultSets,
   APR_CORP_1.dbospAPCOUNTRYVIEWSORTBYLATESTMODIFIEDDATE2().ResultSets.Table1
)

And then your gallery items should be

APCountryTableResultSets

and yeah, preview features should never be used in any app that is user-facing(prod) as they can break without warning.. but if it's not working then either the query is not returning a static table with identical schema every time as described in the docs or its a bug

[HartHenry]

I have been trying for a couple of days to get direct SP calls working in Power Apps, and I have come to the conclusion that it just does not work.  Regardless of what the doc states, this feature is just too new, and unsupported by Microsoft, that it is not working.  BTW, nobody creates a stored procedure that simply returns "Hello World" if that is MS's demo. Most SP(s) returned some type of scaled table.  Simple values usually come from scaler functions (which also is not supported).  Guess it is time to use a heavy custom connector

[Hareesh-Nayaka]

Hi Everyone,

I'm also facing this issue in client environment. In my dev environment everything working fine.

Please suggest me How can i overcome this issue.

[JoannaBulin]

It also doesn't work for me. What helped, were the steps from the section 'Untyped result' from this article: https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/connections/connection-azure-sqldatab....