cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KroonOfficeSol
Level 10

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

 

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
KroonOfficeSol
Level 10

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

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.

View solution in original post

5 REPLIES 5
mr-dang
Level 10

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

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
test - array length.png

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

Microsoft Employee
@8bitclassroom
Islam
Level: Power Up

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

A

KroonOfficeSol
Level 10

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

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.

View solution in original post

KroonOfficeSol
Level 10

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

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.

Highlighted
sarveshshinde15
Level: Powered On

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

@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.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (5,867)