cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ajm456
Level: Powered On

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
Community Support Team
Community Support Team

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
Community Support Team
Community Support Team

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

ajm456
Level: Powered On

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

ajm456
Level: Powered On

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

ajm456
Level: Powered On

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
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
Users Online
Currently online: 257 members 6,536 guests
Please welcome our newest community members: