I managed to complete this flow. Hoping if someone found this, hoping to provide you some information.
Let's say you have a SQL Stored Procedure that accepts a parameter. The result of the procedure is based on the parameter being passed.
For example usp_get_Customers 'Company 1' or it can be 'Company 2' etc.
They return same number and name of columns but different records.
I've created a new Excel file with 3 different sheets and saved it in my BLOB or One Drive or any location you will point the flow
Make sure you named the sheets according to what you want.
Now in the flow, to start this.
1. Create an array of string that contains the names similar to the sheets in your Excel file.
2. Create an array of string parameter values you need to pass in the Stored Procedure
3. Create a variable of Integer and set it to 0, this acts as Index of looping for the variable in Step 2.
4. Create a variable of blank String this should be the place holder of value being looped in Step 2.
5. Create a variable of boolean, I named it FileIsLocked and set it to true. (This is for the creation of Excel file in the User's One Drive)
Next thing I did, I need to copy the file from BLOB to the User's One Drive.
6. Point the BLOB to the location of the file
7. Create Do Until with condition that points to our variable in step 5.
Note: We need to do this because sometimes it returns an error that the file is still locked. (Look in the other articles for details)
8. Inside the DO Until add an action Create File from One Drive. Just fill up the File name you want then get the dynamic value generated from the body of the BLOB
9. Still inside the Do Until Update the FileIslocked value. Make sure this action is set to run whether it is Successful or Has failed.
10. After the DO until create an Apply to each and loop through the variable we created from step 1
11. Inside the loop, set the value of variable from step 4 into this: variables('TableNames')[variables('TableIndex')]
13. Inside the loop, Add an Execute Stored Procedure action then add the parameter current item from step 10 which are the name of the Sheets
14. Add an action Create table from Excel. Take note that you need to use the Id from One Drive in the File properties.
15. Next is just increment the variable from step 3
16. Still inside the Apply to Each loop, add an If Condition that check if there are records being returned from stored procedure:
empty(body('Execute_stored_procedure_(V2)')?['resultsets']?['Table1'])
17. When the condition is True, Add Apply to Each to the body of the result set:
body('Execute_stored_procedure_(V2)')?['resultsets']?['Table1']
18. Inside the Apply to Each, Add an action Add a row into a table
File property point to the Id from Step 8
Table property points to the Title from Step 14
Row property make sure follow the format in the screenshot. The value for each is: item('Loop_DB_Row')?['YourColumnName']
Hope this helps!
