CODEWITHSUNDEEP
sql-serverpower-automate
Felix Froschauer
Felix Froschauer

Reputation: 93

Loop each element of a stored Procedure ResultSet in PowerAutomate(SQL Server)

I want to add an action to my Flow which loops through all the items in a ResultSet and adds them to an SQL Server Table.

The Result set is retrieved using a stored procedure like shown here:

stored procedure

the return values look like this:

enter image description here

I can access the "ReturnCode" if using an SQL Server "Insert Row" Action like this:

enter image description here

but I have no idea how to perform the insert action for each element in this ResultSet, since I cannot access it when using a foreach Action:

enter image description here

forgive me if the solution is obvious, I'm new to PowerAutomate and can't quite figure out how return types are processed. Also if any required information is missing, please tell me so I can add it here.

Upvotes: 1

Views: 2731

Answers (1)

Murilo Santana
Murilo Santana

Reputation: 665

Your response looks like a JSON to me so, using that you could use the "Parse JSON" cction in flow to create an "object" that you can use its properties.

For example:

Your JSON response:

{
    "glossary": {
        "title": "example glossary",
        "GlossDiv": {
            "title": "S",
            "GlossList": {
                "GlossEntry": {
                    "ID": "SGML",
                    "SortAs": "SGML",
                    "GlossTerm": "Standard Generalized Markup Language",
                    "Acronym": "SGML",
                    "Abbrev": "ISO 8879:1986",
                    "GlossDef": {
                        "para": "A meta-markup language, used to create markup languages such as DocBook.",
                        "GlossSeeAlso": [
                            "GML",
                            "XML"
                        ]
                    },
                    "GlossSee": "markup"
                }
            }
        }
    }
}

enter image description here

When you paste that in the "Generate from Template" dialog and click "Done", you will get a schema like the one below:

{
    "type": "object",
    "properties": {
        "glossary": {
            "type": "object",
            "properties": {
                "title": {
                    "type": "string"
                },
                "GlossDiv": {
                    "type": "object",
                    "properties": {
                        "title": {
                            "type": "string"
                        },
                        "GlossList": {
                            "type": "object",
                            "properties": {
                                "GlossEntry": {
                                    "type": "object",
                                    "properties": {
                                        "ID": {
                                            "type": "string"
                                        },
                                        "SortAs": {
                                            "type": "string"
                                        },
                                        "GlossTerm": {
                                            "type": "string"
                                        },
                                        "Acronym": {
                                            "type": "string"
                                        },
                                        "Abbrev": {
                                            "type": "string"
                                        },
                                        "GlossDef": {
                                            "type": "object",
                                            "properties": {
                                                "para": {
                                                    "type": "string"
                                                },
                                                "GlossSeeAlso": {
                                                    "type": "array",
                                                    "items": {
                                                        "type": "string"
                                                    }
                                                }
                                            }
                                        },
                                        "GlossSee": {
                                            "type": "string"
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}

Depending on the JSON response you, you might want to rename a few properties in your schema or even change their type - that will help you to see the properties in you next action:

enter image description here

EDIT: Your objects of type array (from the schema) can be used in the Foreach loop

Upvotes: 1

Related Questions