Search code examples
azure-sql-databaseazure-logic-appsazure-logic-app-standard

Run dynamic stored procedure from azure logic app

I want to run multiple stored procedures from logic app for Azure SQL database. I want names of the stored procedure to be calculated based on a variable name.

I have a variable with values (API_test1_SP1, API_test2_SP1, API_test3_SP1).

In a for loop, I want to run these stored procedures API_test1, API_test2 and API_test3.

I want to remove _SPI from the variable names and run the stored procedures (API_test1, API_test2, API_test3) for Azure SQL database.

I tried following expression without luck

@{concat(API_,slice(@{variables('variable_name')},1,lastIndexOf('_')))}

Is it possible to run stored procedure like this in logic app?

Solution

  • You can use the below expression to achieve your requirement.

    first(split(variables('Array')?[iterationIndexes('Until')],'_SP1'))

    To reproduce the issue, I have used the below flow in my logic app.

    enter image description here enter image description here

    RESULTS:

    enter image description here

    Below is the codeview of my logic app

    {
    "definition": {
        "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
        "actions": {
            "Initialize_variable_-_array": {
                "inputs": {
                    "variables": [
                        {
                            "name": "Array",
                            "type": "array",
                            "value": [
                                "API_test1_SP1",
                                "API_test2_SP1",
                                "API_test3_SP1"
                            ]
                        }
                    ]
                },
                "runAfter": {},
                "type": "InitializeVariable"
            },
            "Initialize_variable_-_loop": {
                "inputs": {
                    "variables": [
                        {
                            "name": "loop",
                            "type": "integer",
                            "value": 0
                        }
                    ]
                },
                "runAfter": {
                    "Initialize_variable_-_array": [
                        "Succeeded"
                    ]
                },
                "type": "InitializeVariable"
            },
            "Until": {
                "actions": {
                    "Compose": {
                        "inputs": "@first(split(variables('Array')?[iterationIndexes('Until')],'_SP1'))",
                        "runAfter": {},
                        "type": "Compose"
                    },
                    "Increment_variable": {
                        "inputs": {
                            "name": "loop",
                            "value": 1
                        },
                        "runAfter": {
                            "Compose": [
                                "Succeeded"
                            ]
                        },
                        "type": "IncrementVariable"
                    }
                },
                "expression": "@equals(variables('loop'), length(variables('Array')))",
                "limit": {
                    "count": 60,
                    "timeout": "PT1H"
                },
                "runAfter": {
                    "Initialize_variable_-_loop": [
                        "Succeeded"
                    ]
                },
                "type": "Until"
            }
        },
        "contentVersion": "1.0.0.0",
        "outputs": {},
        "parameters": {},
        "triggers": {
            "manual": {
                "inputs": {
                    "schema": {}
                },
                "kind": "Http",
                "type": "Request"
            }
        }
    },
    "parameters": {}
}