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!
