Skip to main content
A
ashutoshs3459
January 12, 2024
Question

Stored Procedure Array Input

  • January 12, 2024
  • 11 replies
  • 0 views

Can we pass a list of numbers to input parameter of SP? Is it possible.

a!storedProcedureInput(
name: "group_ids",
value:local!groupids (It is a list of number)

),

It's not working as input parameter is defined as INT on DB side but we are passing list of integers.

    11 replies

    konduruc733182
    konduruc733182
    January 12, 2024

    Hello ashutoshs3459,

    You can pass array values. Try the using {}/{ local!groupIds } in your value parameter.

      A
      ashutoshs3459Author
      January 12, 2024

      Have tried this.Even tried by passing 2 values in {}. It's not working.

      Also, already a list so no need of {}

      rithanir5887
      rithanir5887
      January 12, 2024

      How do I pass a list as a parameter in a stored procedure?

      mikes0011
      mikes0011
      Brainy
      January 12, 2024

      Pass in a list as a comma-separated string, then in the procedure itself you can use functions like FIND_IN_SET (depending on exactly what you need to do with it of course), to act directly on the list.

      ganeshbabuj
      ganeshbabuj
      August 12, 2025

      For MariaDB - Plus we need to make sure there is no comma in the value passed in, when Appian build the query space gets added before all value so use below on where condition on Procedure

      AND (p_input IS NULL OR p_input = '' OR FIND_IN_SET(TABLE.COLUMN_NAME, REPLACE(p_input,' ','')) > 0)

      S
      shreekrit518231
      January 12, 2024

      1st Approach -  you can pass in string format and can split the string by the seprator or find_in_set

      2nd approach: 

      create one expression rule in which use forEach Expression and pass your array of input as items and in expression call your stored procedure smart function (executeStoredProcedureForQuery) to execute. see eg below - 

      a!localVariables(
      local!groupsIds: {},
      reject(
      a!isNullOrEmpty,
      a!flatten(
      a!forEach(
      items: local!groupsIds,
      expression: a!localVariables(
      local!resp: a!executeStoredProcedureForQuery(
      dataSource: "dataource_name",
      procedureName: "procedure_name_in_db",
      inputs: {
      a!storedProcedureInput(name: "group_ids", value: fv!item)
      }
      ),
      if(
      local!resp.success,
      local!resp.results,
      null()
      )
      )
      )
      )
      )
      )

      Terms & ConditionsAccessibility statement