SQL Execute Stored Procedure - Data Gateway - returns no parameters and returncode

[KroonOfficeSol]

I have a problem with the sql execute stored procedure element in flow, which doesn't return the output parameters and returncode.

The issue occurs only when connecting to an on premise sql server through the data gateway and not when using a sql server in Azure.

The returned data with Azure

{
  "OutputParameters": {
    "outputDec": 880,
    "OutputInt": 1,
    "OutputStr": "SO71774"
  },
  "ReturnCode": 1,
  "ResultSets": {
    "Table1": [{}]

The returned dataset with the Data Gateway

{
  "OutputParameters": {},
  "ResultSets": {
    "Table1": [{}]
    

The main issue is that I need to use a condition to determine if 

"Table1": [{}]

 exist in my resultsets. I use a respons request to return the results to PowerApps and if Table1 does not exists my flow (and PowerApp) break. The condition if false should fix this.

Questions:

1. is this issue by design off the data gateway and is it impossible to return the parameters and returncode? Or is this a bug and should it be normal behaviour that this items should be returned to flow?
2. Is there a workaround in which I can check if the ResultSets contain the Table1? I this way I can base my condition on Table1 to exists or not.

[mr-dang]

Hi @KroonOfficeSol,

I am thinking you can attempt to do a count of the rows using an expression with length().

Here's a simplified example of using length

Here I initialized an array variable, but I didn't put anything in it. Then in the compose step, I used an expression to count how many records was in the array--we're expecting 0.

length(variables('test'))

In your case, you'll want to check a condition if the length is 0, then perform actions then.

equals(length(body('name_of_your_step')['ResultSets']['Table1']),0)

Let me know if that or part of that works for you.

Brian

[Islam]

A

[KroonOfficeSol · Accepted solution]

He @mr-dang, Thanks for you're respons. 

I thought off the length() solutions, but found out it didn't work. The reason is that ['Table1'] doesn't exist in the respons so the flow breaks on that parameter. I then tried the length() without the ['Table1'] part, but this gave an error that it expects a string and not a object or array. Then I found the solution which is:

@not(empty(body('Execute_stored_procedure')?['resultsets'])) 

This works for the condition and fixes the flow.

I have now another challenge at the side off PowerApps and the ClearCollect() function. This function gives a 503 error when there is no returned table. For the browser this is no problem, but on a mobile you get an red error message. Maybe you could help me with a fix for this challenge?

I think off two lines off solutions:

1. PowerApps - is there a way to catch and repress the errormessage (so don't show it to the user)
2. Flow - is there a way to create a ['Table1'], which contains the headers from my sql output, but with no record.

Hope you can help.

[KroonOfficeSol]

For everybody who comes to this threat. As a solution to this issue I added a extra record to my select statement at the end off the stored procedure, for this I use  the formula

Union All Select Null,Null, Null, Null, Null

(as much Nulls as the number off columns to return).

So in the case there are no records in my select statement I still return a resultsets with a table1 containing the one record. Then I remove the last record from my collection in PowerApps right after the ClearCollect(). So with no results the collection will be empty, but the flow runs without errors.

OffCourse still like to get a solution to repress the 503 error message in PowerApps if possible.

[sarveshshinde15]

@KroonOfficeSol  Nice alternative.

I am also facing same issue and wants to supress the error shown in PowerApps. 

Hopefully there is better way to supress the error. Even I tried using IfError function. That also didnt help. It still shows badgateway error when there are no rows returned from your store proc.