Answered

SQL server stored procedures - can't access multiple tables, just the first one

(1) Share

Report

Posted on by MB-23070938-0

I am testing SQL Stored procedure feature from Canvas Apps. I have a stored procedure with 3 SELECT statements, which should return results to powers apps as Table1 and Table2, Table3 etc However, the Table 2 and others always come up as 'Could not find any data'.

Microsoft Learn says it should work:

Canvas app call for stored procedure:

 Set(varItemsForAll,  
                    ForAll(Table(TESTDB.dboFindPersonDetails({PID: varPID}).ResultSets.Table1) As PersonTable1,
                    {
                        PersonInstanceID: Value(PersonTable1.Value.PersonInstanceID),
                        PersonID: Value(PersonTable1.Value.PersonID),
                       
                    
                    })

            );

 Set(varItemsForAll3,  
                    ForAll(Table(TESTDB.dboFindPersonDetails({PID: varPID}).ResultSets.Table3) As PersonTable3,
                    {
                        PersonInstanceID: Value(PersonTable3.Value.PersonInstanceID),
                        PersonID: Value(PersonTable3.Value.PersonID),
                       
                    
                    })

            );

SQL:

ALTER Stored Procedure dbo.FindPersonDetails
@PID int

AS 
BEGIN
SET NOCOUNT ON

-- Table 1
SELECT PersonInstanceID, PersonID
FROM Person
WHERE PID = @PID

-- Table 2
SELECT PersonInstanceID, PersonID
FROM Person
WHERE PID = @PID


-- Table 3
SELECT PersonInstanceID, PersonID
FROM Person
WHERE PID = @PID


END

No error, just comes up as empty table in the variable (Table1 works as expected, though all 3 select states are the same)

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Sort by

MB-23070938-0 48 on at

Like (1)

Report
Copy link

Link copied!

Forgot to add the monitor says there was only 1 table in response.

Was this reply helpful? Yes No
developerAJ 4,763 on at

Like (0)

Report
Copy link

Link copied!
When adding the stored procedure as a data source in PowerApps, make sure to tick the "Safe for Use in Gallery" checkbox. This will allow PowerApps to correctly recognize and process multiple result sets.

Set(varItemsForAll, TESTDB.dboFindPersonDetails({PID: varPID}).ResultSets)

varItemsForAll.Table1 for the first result set

varItemsForAll.Table2 for the second result set

varItemsForAll.Table3 for the third result set

Was this reply helpful? Yes No
developerAJ 4,763 on at

Like (0)

Report
Copy link

Link copied!

Try changing the query for each one, as PowerApps is not recognizing it despite using three different SELECT statements. Also, execute it in SQL and check the output.

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

Like (0)

Report
Copy link

Link copied!
I've re-added the stored procedures making sure the gallery safe option is on. Unfortunatelly that doesn't change anything.
Set(varItemsForAllx, TESTDB.dboFindPersonDetails({PID: varPID}).ResultSets); Set(varItemsForAll2, ForAll(Table(varItemsForAllx.Table2) As TestTable2, { PID: Value(TestTable2.Value.pid) } ) );

On Monitor response body ResultSets come back with just Table1.

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

Like (0)

Report
Copy link

Link copied!

I did have different SELECT there, then changed to same to see if it was issue with the query, then changed back to different, then same :D

When I execute on the server SQL returns 3 tables, either same or different ones.

Was this reply helpful? Yes No
Verified answer

MB-23070938-0 48 on at

Like (1)

Report
Copy link

Link copied!
Apparently multiple results sets only available from Azure! On premises can't return multiple data sets. Which makes it very frustrating...

Limitations:

The following limitations apply to invoking a stored procedure on an on-premises SQL server:

Output values for OUTPUT parameters aren't returned. You can still specify input values for OUTPUT parameters.

ResultSets value is Untyped.

Return value isn't available.

Only the first result set is returned.

Dynamics schemas aren't supported for result sets.

1 people found this reply helpful.

Was this reply helpful? Yes No