Hi I am new to SuiteQL and I wrote a function in my NS script that retrieves items based on their category.

export const getItemsByCategory = ({ categoryIds }) => {
  return query.runSuiteQL({
    query: `SELECT item.id, itemid as name
            FROM item 
            WHERE  custitem_category in (?)`,
    params: categoryIds
  }).asMappedResults()
}

categoryIds is an array of ids. When I pass in one id, it works, but when I pass in 2 ids:

getItemsByCategory({categoryIds:[224,155]})

I get this error:

message: 'Invalid number of parameters. Expected: 1. Provided: 2.',
  object: {
    type: 'error.SuiteScriptError',
    name: 'INVALID_NUMBER_OF_PARAMETERS',
    message: 'Invalid number of parameters. Expected: 1. Provided: 2.',

Can anyone help?

the params parameters has specific functionality for arrays. honestly I would describe it such that params is supposed to be an array

I usually do something like this, though it is technically vulnerable to sql injection so might need to sanitize the ids if that is a concern

export const getItemsByCategory = ({ categoryIds }) => {
  return query.runSuiteQL({
    query: `SELECT item.id, itemid as name
            FROM item 
            WHERE  custitem_category in (${categoryIds.join(",")})`
  }).asMappedResults()
}

try

getItemsByCategory({categoryIds:['224,155']})

params accept an array of strings, numbers or booleans. I believe each item in the array it mapped to each "?" in the order that they are entered in the SuiteQL.

Thanks for all your replies ! @Watz tried your suggestion but now got a different error: "Search error occurred: Invalid or unsupported search" . @michoel had a similar solution to yours that worked but was worried about sql injection. How do you protect against that? I'm not so familiar....

It might need to be '"224","155"'

@Watz thanks but that gave the same error: "Search error occurred: Invalid or unsupported search"

IMO the SQL injection risk in minimal considering that SuiteQL is read-only, and should generally be running under the same access the user anyways has (unless script has elevated permissions). I'd love to hear others' opinion on this. To sanitize you could do something like

const sanitizedCategoryIds = sanitizedCategoryIds.map((e) => Number(e));

. But thinking this through, probably the best option would be to use multiple placeholders:

export const getItemsByCategory = ({ categoryIds }) => {
  return query.runSuiteQL({
    query: `SELECT item.id, itemid as name
            FROM item 
            WHERE  custitem_category in (${categoryIds.map((e) => "?").join(",")})``,
    params: categoryIds
  }).asMappedResults()
}

@michoel, looks better. I wouldn't worry about SQL-injection either in this case.

Thanks @michoel and @Watz! very helpful!