web
You’re offline. This is a read only version of the page.
close
Skip to main content
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps - Connector Development / Stored Procedure with ...
Power Apps - Connector Development
Answered

Stored Procedure with Parameters output to Data Table

(0) ShareShare
ReportReport
Posted on by AK-29071205-0 9
I'm trying to populate a Power Apps Data Table with the output from a Stored Procedure after passing just one parameter from Power Apps to SQL Server (ideally with more parameters, but for the sake of getting it to work, just one for now).
 
The simple setup I have is as follows:
 
- One SCREEN named snStoredProcTest
- One TEXT INPUT named txtProgName
- One BUTTON named btnRunStoredProc
- One DATA TABLE named dtStoredProcTest
- My Stored Procedure has been connected like an external data source to my Power App and is named ConnName.ppspSearchprog ("Safe to use for galleries and tables?" is checked yes)
- My Stored Procedure has been tested in SQL Server using EXEC and the relevant parameter and returns 101 records with no issue
 
 
In the BUTTON's 'OnSelect' Action Property, I have the following:
 
ClearCollect(
    colSearchResults,
    ConnName.ppspSearchProgs(
        {
            progName:(txtProgName.Text)
        }
    )
)
 
Please Note: the parameter of the Stored Procedure, "progName:", did populate in the intellisense which leads to me to believe Power Apps can see and is reading the Stored Procedure.
 
In my DATA TABLE's 'Items' Data Property, I have the following Collection listed:
 
colSearchResults

Please Note: this is the variable from the ClearCollect function in my BUTTON's 'OnSelect' Action Property.
 
When I run the app and click the BUTTON with a short text value that has been tested in SQL Server, the Stored Procedure runs with no errors, but no records display, and the Collection only shows (Table: 1 rows) which is nothing more than a question mark under OutputParameters and ResultSets (nothing under ReturnCode).

I'm unable to add the field names to the DATA TABLE unless I bind the table to a data connection which still displays no records.

If you have done this successfully, please advise what I'm missing. I've been at this for 48 hours and cannot find a solution in Microsoft Doc's, AI, or online forums or videos. It seems like it should be pretty straight forward but I'm at a loss.
Categories:
Authentication
Policy Templates
I have the same question (0)
  • Power Apps 1919 Profile Picture
    Power Apps 1919 604 on at
    Stored Procedure with Parameters output to Data Table
    Hi @AK-29071205-0, glad that solved your question.
    Please make sure to close the thread, so that it can be helpful for others.
    Please let me know if you need any help from me.
  • Verified answer
    AK-29071205-0 Profile Picture
    AK-29071205-0 9 on at
    Stored Procedure with Parameters output to Data Table
    So, I got it to work, and yes, it's an SQL Server View
     

    I had to wrap the stored procedure in Table() and add ".ResultsSets.Table1" as it was originally returning a single record as "Data Type: Record" when hovered over. So now it looks like this: 

    ClearCollect(

        colSearchResults,

        Table(

                ConnName.ppspSearchProgs(

                {

                    progName:(txtProgName.Text)

                }

            ).ResultSets.Table1

        )

    )

    Then once it was run, the first (and in this testing example only field), showed up and the DATA TABLE field TEXT property was changed to 

    ThisItem.Value.[FieldName]

    For us: ThisItem.Value.Prog

  • Suggested answer
    Power Apps 1919 Profile Picture
    Power Apps 1919 604 on at
    Stored Procedure with Parameters output to Data Table

    when calling SQL stored procedures from Power Apps. If your proc queries a schema-defined (physical) table - doing things like filtering, sorting or pagination - Power Apps receives a fully typed table with the original column metadata. However, if you build your result set in a temporary table instead, Power Apps only gets the raw data as untyped objects, since the temp-table schema isn’t sent back.

    please check this article: View results in SQL Server - Power Apps | Microsoft Learn

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities
Announcing our 2025 Season 2 Super Users!
Responsible AI policies for the Community

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Telen Wang – Community Spotlight

We are honored to recognize Telen Wang as our August 2025 Community…

Congratulations to the July Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 637 Most Valuable Professional

#2
stampcoin Profile Picture

stampcoin 570 Super User 2025 Season 2

#3
Power Apps 1919 Profile Picture

Power Apps 1919 473

Last month Overall leaderboard

Featured topics

Product updates

Microsoft Power Platform Community release plans