How-To: Return result of a Select Count query from a SQL Server Stored Procedure to PowerApps

[LRVinNC]

Well, that title was a mouthful, wasn't it?  It's about as long as the statement to accomplish this simple task! 

Seems like it should be an easy, right?  You want to count the records in a SQL Server on-premise table and return that count to PowerApps. If this was in the cloud, it would be simple to use Execute a SQL Query, but that isn't available on-prem.  Therefore you must use a Stored Procedure to accomplish the count, albeit a very simple stored procedure which only consists of a single statement:  Select Count(<columnname>) from <tablename>

Since you can't invoke a Stored Procedure directly from PowerApps (that idea is currently under review), you'll have to get by with a little help from PowerApps' friend Flow.  But you can't ask for a much simpler flow. 

Your trigger will be PowerApps, followed by a SQL Server - Execute Stored Procedure action which simply executes the stored procedure you created above, then you'll add a Respond to PowerApps action.  That's the only "tricky" part of the flow.  You can't use the normal response offered via dynamic content -- it will only provide you with ReturnCode which does you absolutely no good.  Instead, you'll need to create your own expression to return:

body('<action name - mine was Step2>')?['ResultSets']?['Table1']

I should note, you can also use HTTP Response as outlined in Brian Dang's video, but the value returned to PowerApps will be the same and Respond to PowerApps is now the simpler approach. In addition, Brian was executing a SQL Query, not a Stored Procedure which I had to use to access my on premise table. 

OK, let's wrap up in Flow by naming and saving your flow, then head on back over to PowerApps.

In a perfect world, the output from the flow would simply get passed back to PowerApps ready to use - in this case as a count of the number of records in that SQL table.  But where would the fun be in that? LOL  Stay with me kids because it's going to get a little hairy here.

The problem is, since Flow can't return clean output to us, we have to go fish the output we want out of what Flow CAN give us.  Here's what you are going to get back -- that value "3361" is what we want but it's surrounded by a bunch of junk we don't want and unfortunately, we can't seem to address the "Column1" directly - believe me I tried!

So, we'll just use a little magic to sift the wheat from the chaff:

Set(SQLCount,Last(FirstN(Split(Last(FirstN(Split(Text(PowerAppsbutton.Run().text),":"), 2)).Result,"}"),1)).Result)

I told you it was a mouthful, didn't I?  Let's break that down from the inside out, shall we? 

PowerAppsbutton.Run()
That's simple enough -- run your flow (mine was unimaginatively called PowerAppsbutton).  There is nothing you need to pass to the query, although if you have a need to do a series of counts, you could pass in a specific Stored Procedure name or indicator for Flow to execute (modifying the flow accordingly to use the input parameter).  

Split(Text(PowerAppsbutton.Run().text),":")
Now, take the result being sent back from Flow and split it, using a colon ( : ) as the delimiter. If we look back at the flow results above, we see that [{\"Column1\":3361}] is what is being returned from my flow.  So this split action is going to create a result table with two parts:  [{\"Column1\"  and 3361}]. Ok, we are heading in the right direction, but we still have some junk left in the trunk we need to get rid of before we have a usable value.

Split(Last(FirstN(Split(Text(PowerAppsbutton.Run().text),":"), 2)).Result,"}")

So, we just need to split the second part of the result using a right curly brace ( } ) as the delimiter, right?  Yes, but we have to tell PowerApps how to get to just that second row of the result containing 3361}]. That's where the Last(FirstN comes in.  Last(FirstN(<Table>, <index>)) will return the value stored at the indexed location, in our case in the second row of the Result. Then split can do its stuff to get us just the number, 3361.  Yay!  We're there, right?  Not quite.

Last(FirstN(Split(Last(FirstN(Split(Text(PowerAppsbutton.Run().text),":"), 2)).Result,"}"),1)
Remember that split creates a table of results, with 3361 in the first row and ] in the second.  We only want that first value so we'll need ot use Last(FirstN again, this time with an index of 1.  Now, we're really done, right? Almost.  One more step.

Set(SQLCount,Last(FirstN(Split(Last(FirstN(Split(Text(PowerAppsbutton.Run().text),":"), 2)).Result,"}"),1)).Result)

Our last step is to take the result of that final Last(FirstN (accessed using the .Result on the end above) and store it in our global variable SQLCount with a Set action.

NOW.... we are done.  Our SQLCount is equal to 3361.

Yes, that was painful.  But it did accomplish the job.  Hopefully, either Flow will become capable of directly passing output back to us or that PowerApps idea mentioned above as being Under Review will get Approved and show up in a future PowerApps release.  In the meantime, you might want to keep this is your back pocket.  As I have always told my kids, where there's a will... there's a way. 

(Developer's note:  When building a complex action like this, always start by doing it in separate parts each saved into their own variable so you can validate each one works as intended.  Once you feel good about your logic,  then go back and combine all those individual actions into one.  It's a whole lot easier to debug that way!)