Answered

Calling a SQL Server Stored Procedure from ClearCollect

(0) Share

Report

Posted on by CU25081109-1

Hello.

I am trying to call a SQL Server stored procedure from a ClearCollect, to put the results into a collection, so I can disply the results in a datatable.

My code is -

Clear(collectionSearchDetails);
ClearCollect(collectionSearchDetails, Legionella_dataSQL.dboSearchPatientDetailsByName({Forename: txt_SearchByNameForename.Text, Surname: txt_SearchByNameSurname.Text}));
If (IsEmpty(collectionSearchDetails), Notify("No Records Returned", ErrorSeverity.Warning));
Navigate(scr_NameSearchResults, ScreenTransition.Fade);
 
My stored procedure is a simple select - 
 
ALTER PROCEDURE [dbo].[SearchDetailsByName]

    @Forename NVARCHAR(50) = NULL,

    @Surname NVARCHAR(50) = NULL

AS

BEGIN

    SET NOCOUNT ON
    SELECT

        *

    FROM

        details

    WHERE

        (@Forename IS NULL OR forename LIKE '%' + @Forename + '%')

        AND (@Surname IS NULL OR surname LIKE '%' + @Surname + '%');

END;
 
When I execute, the stored procedure runs and brings back the right details.
 
However, when I look at the collection there is one record which is blank.  The collection does not have any data or resultsset in it.
 
What am I doing wrong?
 
Thanks
 
Kevin

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (1)

Suggested answer

Power Platform 1919 2,205 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @CU25081109-1 ,
please refer this documentation. and if possible, can you share the screenshot/code of what you're seeing in power apps
View results in SQL Server - Power Apps | Microsoft Learn

Was this reply helpful? Yes No
Suggested answer

Pstork1 69,129 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

A Stored Procedure will return the data set of results as a Table in a property called ResultSets in an object. Your ClearCollect is trying to load the object as a collection. You need to find the actual name of the Table being returned as a Property and append that to the Stored Procedure call inside the clear collect to get the array of data instead of the object. Something like this

ClearCollect(collectionSearchDetails, Legionella_dataSQL.dboSearchPatientDetailsByName({Forename: txt_SearchByNameForename.Text, Surname: txt_SearchByNameSurname.Text}).ResultSets.Table1);

----------------------------------------------------------------------------------
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
CU25081109-1 49 on at

Like (0)

Report
Copy link

Link copied!

Hi Have tried -

ClearCollect(collectionSearchDetails, Legionella_dataSQL.dboSearchPatientDetailsByName({Forename: txt_SearchByNameForename.Text, Surname: txt_SearchByNameSurname.Text}).ResultSets.Table1);

But get error - The function 'ClearCollect' has some invalid arguments. And, Invalid argument type. Cannot use Dynamic values in this context.

Was this reply helpful? Yes No
Pstork1 69,129 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

I would temporarily try loading the entire output of the stored procedure into a variable instead of a collection. Then you can look at the variable and find the exact names of the results property and table name. Once you have those you should be able to fix the ClearCollect statement.

The dynamic results error is because the table is untyped data. YOu can read how to fix that here: View results in SQL Server - Power Apps | Microsoft Learn

----------------------------------------------------------------------------------
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
CU25081109-1 49 on at

Like (0)

Report
Copy link

Link copied!

I changed the ClearCollect to Set into a variable -

Set(var_SearchResults, Legionella_dataSQL.dboSearchPatientDetailsByName({Forename: txt_SearchByNameForename.Text, Surname: txt_SearchByNameSurname.Text}));

To see what I get. I get the following

So ResultSets.Table1 should be right.

Banging my head against a brick wall!

Was this reply helpful? Yes No
Power Platform 1919 2,205 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @CU25081109-1,

The problem is the power app is not able to identify the schema of data returned from sql proc, so u get dynamic object/type error.

To tackle you have to convert the result as standard schema I.e use parseJson function + forall or addcolumns+ParseJson combo and build the scheme which power apps can use.

Please refer to the documentation links provided by me or @Pstork1.

For more information

Link: https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/connections/sql-connection-view-results#dynamic-results

Was this reply helpful? Yes No
Verified answer

stampcoin 5,146 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
@CU25081109-1 Hi,

I would like to join the conversation.

Try below code :
( for example, only fetch forename and surname, two columns, you can add more columns)

Set( var_SearchResults, Legionella_dataSQL.dboSearchPatientDetailsByName( { Forename: txt_SearchByNameForename.Text, Surname: txt_SearchByNameSurname.Text } ).ResultSets.Table1 ); Set( MyTable, ForAll( var_SearchResults,// extract JSON from table1 { Forename: Text(ThisRecord.forename), Surname: Text(ThisRecord.surname) } ) );

then use MyTable for gallery/table as example to view the data

Was this reply helpful? Yes No
Suggested answer

Power Platform 1919 2,205 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Hi @CU25081109-1,
Adding my way of Handling Proc (Power Fx):
ClearCollect( col_demo_data, With( { // 1) Base table from your stored procedure/API // Each row has a single UntypedObject column named "Value" // Note: sometimes you need JSON() around the proc call, sometimes not. // It depends on whether your connector returns a Dynamic Object. // If one fails, try the other pattern. data: Table( ParseJSON( JSON( MyDatabase.RunMyStoredProc().ResultSets.Table1 ) ) ) }, // 2) Safely project typed columns and drop the "Value" carrier IfError( DropColumns( AddColumns( data, // Make sure to use the exact field names from your JSON/proc // Example JSON keys: ACCOUNT_OWNER_NAME, CHANNEL, EMPLOYEE_ID, JOINING_DATE, ACTIVE // TEXT ACCOUNT_OWNER_NAME, Text(ThisRecord.Value.ACCOUNT_OWNER_NAME), CHANNEL, Text(ThisRecord.Value.CHANNEL), // NUMBER (INT/DECIMAL → Value()) EMPLOYEE_ID, Value(ThisRecord.Value.EMPLOYEE_ID), SALARY, Value(ThisRecord.Value.SALARY), // DATE (ISO date string → DateValue) JOINING_DATE, DateValue(ThisRecord.Value.JOINING_DATE), // BOOLEAN (coerce true/false; adjust if your value returns text) ACTIVE, If( IsBlank(ThisRecord.Value.ACTIVE), false, Boolean(ThisRecord.Value.ACTIVE) ) ), "Value" // remove the original untyped object column ), // 3) Error fallback: return empty table so collection won't break Table() ) ) )

Was this reply helpful? Yes No