Answered

Checking for empty stored procedure result

(0) Share

Report

Posted on by ajm456

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)?

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

v-xida-msft on at

Like (3)

Report
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:
The flow works successfully as below:
The returned result of Excute stored procedure action as below:

More details about limitations of SQL Connector, please check the following document:
Known Issues and Limitations

Best regards,
Kris

Was this reply helpful? Yes No
ajm456 on at

Like (0)

Report

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:

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:

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

Kind regards,
Andy

Was this reply helpful? Yes No
ajm456 on at

Like (0)

Report
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:
Completely at a loss for what the problem here is.

Kind regards,
Andy

Was this reply helpful? Yes No
ajm456 on at

Like (0)

Report

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

Was this reply helpful? Yes No