Answered

Power Automate OnPrem SQL Stored Procedure execute obtaining Result sets ( multiple rows, Multiple Columns )

Like (0) Share

Report

Posted on 23 Aug 2022 07:15:41 by Community Power Pla...

Hi everyone,

Im executing a stored procedure in an On Prem SQL Server, I havent had much luck obtaining the data in the result sets that is presented back. I can obtain a single value ok , hand have so previously in the past ie return codes, but cannot seem to get a result set of data reliabily. ie multiple rows with multiple columns

Examples are slim pickings ( parse json etc Ivew seen and used but run into errors of one kind or another ).

Anybody have a full example that can be followed? i've scanned the forum

The end game is to obtain the SP rowset and place into an email and send.

Any help appreciated

Mike

All responses (7)

Answers (1)

Verified answer

Community Power Pla... on 08 Sep 2022 at 03:10:21

Like (0)

Report

Re: Power Automate OnPrem SQL Stored Procedure execute obtaining Result sets ( multiple rows, Multiple Columns )

hope this helps someone else...
Community Power Pla... on 08 Sep 2022 at 03:09:19

Like (0)

Report

Re: Power Automate OnPrem SQL Stored Procedure execute obtaining Result sets ( multiple rows, Multiple Columns )

Found a solution to get at the elements, ( there may be other ways but this works ok for me ).

Execute the SQL Stored procedure and in the 'Apply to each' ensure the Output from the previous step is set to address the returned table in the output of the stored procedure, not the Resultset!. This exposes each of the elements such that they can be addressed individually. The line HTML is then wrapped around each of the elements and an HTML Header information is placed in the email.

I did try the Create HTML table action but couldnt get it to work correctly and ran out of development time.
Community Power Pla... on 25 Aug 2022 at 06:24:26

Like (0)

Report

Re: Power Automate OnPrem SQL Stored Procedure execute obtaining Result sets ( multiple rows, Multiple Columns )

for others that might follow..
I have got a bit further , below provides basic output from the stored procedure. Still cannot get at the elements, <br> provides a line feed.
Community Power Pla... on 25 Aug 2022 at 00:38:46

Like (0)

Report

Re: Power Automate OnPrem SQL Stored Procedure execute obtaining Result sets ( multiple rows, Multiple Columns )

I realise the Output of the Resultset has to be an array, but cannot get at the contents of the Resultset array correctly.. ( multiple columns with probably 5-10 rows )
Community Power Pla... on 25 Aug 2022 at 00:36:55

Like (0)

Report

Re: Power Automate OnPrem SQL Stored Procedure execute obtaining Result sets ( multiple rows, Multiple Columns )

Apologies a picture is worth a thousand words

dies with
Community Power Pla... on 23 Aug 2022 at 20:12:58

Like (0)

Report

Re: Power Automate OnPrem SQL Stored Procedure execute obtaining Result sets ( multiple rows, Multiple Columns )

Thanks Kaif,
Im using the SQL exec stored procedure to run the proc. Running the procedure isnt the issue.
Its the working of the resultset ( multiple rows , multiple columns ), so I can assemble it into an email and send
I have a for each look on the ResultSet table,and that loops for the number of rows returned, however, I cannot address the Items within with Items()?[ 'Column Name'].
thanks
Kaif_Siddique 2,104 Super User 2024 Season 1 on 23 Aug 2022 at 18:12:31

Like (0)

Report

Re: Power Automate OnPrem SQL Stored Procedure execute obtaining Result sets ( multiple rows, Multiple Columns )

Hi Majero,

Do you want to retrieve the stored procedure query results(recordset)? If yes, you can call stored procedure using Exce function with parameters to get the query results output. Please refer below

Regards
Kaif