cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Checking for empty stored procedure result

Simple enough question I couldn't find an answer to after a quick forum search: I've got a Flow running a stored procedure (successfully) in my SQL Server database with an input argument. What do I need to put into a subsequent Flow condition to check if my stored procedure executed successfully, but returned no rows (i.e. the dynamic data is empty)?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Checking for empty stored procedure result

Hi @ajm456,

 

Which SQL connector do you use? On-premises SQL Server or SQL Server in Cloud service?

Further, could you please share more details about your stored procedure?

There are some limitations when we invoke a Stored Procedure on-premise SQL Server:

  • Output value for OUTPUT parameters are not returned.
  • Return value is not available.
  • Only one result set is returned.

I have made a test on my side and the flow works well as below:12.JPG

 The flow works successfully as below:13.JPG

 The returned result of Excute stored procedure action as below:14.JPG

 

More details about limitations of SQL Connector, please check the following document:

Known Issues and Limitations

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Highlighted
Community Support
Community Support

Re: Checking for empty stored procedure result

Hi @ajm456,

 

Which SQL connector do you use? On-premises SQL Server or SQL Server in Cloud service?

Further, could you please share more details about your stored procedure?

There are some limitations when we invoke a Stored Procedure on-premise SQL Server:

  • Output value for OUTPUT parameters are not returned.
  • Return value is not available.
  • Only one result set is returned.

I have made a test on my side and the flow works well as below:12.JPG

 The flow works successfully as below:13.JPG

 The returned result of Excute stored procedure action as below:14.JPG

 

More details about limitations of SQL Connector, please check the following document:

Known Issues and Limitations

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Frequent Visitor

Re: Checking for empty stored procedure result

Hi Kris

 

Thanks for your reply.

 

SQL Server is on Cloud, and my stored procedure, a simple SELECT with a pair of parameters, runs as expected when executed just as a query outside of Flow. My Flow is as below:

Capture.PNG

 

As you can see, the condition field is copied from yours (I've never encountered the advanced editor syntax before) and I get the following error when running:

Capture2.PNG

 

I'm completely new to Flow - I've probably done something silly and shouldn't be too difficult to fix Smiley Happy

 

Kind regards,

Andy

Highlighted
Frequent Visitor

Re: Checking for empty stored procedure result

To continue with this...

 

I've now got the conditional successfully checking for empty results with:

@not(empty(body('<Action_name>')?['resultsets']?['Table 1']))

My problem is now that I am unable to choose an action in the "If yes" and "If no" sections after the condition:

Capture3.PNG

Completely at a loss for what the problem here is.

 

Kind regards,

Andy

Highlighted
Frequent Visitor

Re: Checking for empty stored procedure result

Closing this as I have now got the entire Flow working as intended. Thank you @v-xida-msft for your help Smiley Happy

Helpful resources

Announcements
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

Join the new Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

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

Top Solution Authors
Top Kudoed Authors
Users online (6,836)