Build Dynamic SuiteQL WHERE Clauses Without Breaking Bind Parameters

Dynamic SuiteQL becomes error-prone when optional filters are added by concatenating strings in several different places.

The SQL can look correct while its positional parameters are in the wrong order. Alternatively, the script can produce a dangling AND, an empty WHERE clause or a JavaScript value that N/query cannot bind.

A small construction pattern avoids all of those problems: append each SQL clause and its parameter values together.

The fragile approach

This style separates the query text from its parameters:

let sql = 'SELECT id, entityid FROM customer WHERE ';
const params = [];

if (subsidiaryId) {
  sql += 'subsidiary = ?';
}

if (email) {
  sql += ' AND email = ?';
}

if (email) params.push(email);
if (subsidiaryId) params.push(subsidiaryId);

If both filters are present, the first placeholder receives the email even though it represents the subsidiary. If only the email is present, the query starts with WHERE AND.

Keep clauses and parameters in lockstep

Build an array of complete conditions and a matching parameter array:

const clauses: string[] = [];
const params: Array<string | number | boolean> = [];

if (subsidiaryId) {
  clauses.push('customer.subsidiary = ?');
  params.push(subsidiaryId);
}

if (email) {
  clauses.push('LOWER(customer.email) = LOWER(?)');
  params.push(email.trim());
}

const where = clauses.length > 0
  ? `WHERE ${clauses.join(' AND ')}`
  : '';

const sql = `
  SELECT
    customer.id,
    customer.entityid,
    customer.email
  FROM customer
  ${where}
  ORDER BY customer.entityid
`;

const results = query.runSuiteQL({
  query: sql,
  params,
}).asMappedResults();

Every ? and its corresponding value are added inside the same if block. That preserves their positional relationship.

Oracle’s query.runSuiteQLPaged() documentation demonstrates the same positional ? placeholders and params array used by N/query SuiteQL methods.

Add multi-value filters deliberately

Generate one placeholder for every value in an IN condition:

if (statusIds.length > 0) {
  const placeholders = statusIds.map(() => '?').join(', ');
  clauses.push(`customer.status IN (${placeholders})`);
  params.push(...statusIds);
}

Never concatenate untrusted values directly into the SQL to avoid creating placeholders. Binding values protects quoting and keeps the query structure separate from its data.

Column names, table names and sort directions cannot be treated like ordinary bind values. If those must be dynamic, choose them from a strict allowlist:

const allowedSorts = {
  name: 'customer.entityid',
  email: 'customer.email',
} as const;

const sortColumn = allowedSorts[input.sort] ?? allowedSorts.name;
const direction = input.descending ? 'DESC' : 'ASC';

Bind dates as formatted strings

A JavaScript Date object is not a safe SuiteQL parameter type. Convert the date to a known string format and convert it inside SQL:

function toIsoDate(value: Date): string {
  return value.toISOString().slice(0, 10);
}

if (startDate) {
  clauses.push(`transaction.trandate >= TO_DATE(?, 'YYYY-MM-DD')`);
  params.push(toIsoDate(startDate));
}

Oracle documents SuiteQL TO_DATE usage with a format matching the supplied date string. Keeping the format explicit makes the boundary between JavaScript and SQL unambiguous.

Be conscious of time zones: toISOString() uses UTC. That is suitable when the input represents a calendar date already normalized to UTC, but a local date near midnight may cross a day boundary. For user-entered NetSuite dates, format the intended calendar components rather than blindly converting a local timestamp.

Do not push null into the parameter list

N/query bind parameters are restricted to supported primitive types. A bare null can produce SSS_INVALID_TYPE_ARG.

Choose the query structure before execution:

if (ownerId == null) {
  clauses.push('customer.custentity_owner IS NULL');
} else {
  clauses.push('customer.custentity_owner = ?');
  params.push(ownerId);
}

This is clearer than trying to make one placeholder mean both “match this value” and “do not filter”.

It also avoids the separate SuiteQL trap where an empty string behaves as NULL rather than as a comparable zero-length value.

Extract repeated filters into a helper

For a large query, a small builder keeps additions consistent:

class WhereBuilder {
  readonly clauses: string[] = [];
  readonly params: Array<string | number | boolean> = [];

  add(sql: string, ...values: Array<string | number | boolean>): void {
    this.clauses.push(sql);
    this.params.push(...values);
  }

  toSql(): string {
    return this.clauses.length
      ? `WHERE ${this.clauses.join(' AND ')}`
      : '';
  }
}

Usage remains explicit:

const where = new WhereBuilder();

if (subsidiaryId) {
  where.add('customer.subsidiary = ?', subsidiaryId);
}

if (startDate) {
  where.add(
    `customer.datecreated >= TO_DATE(?, 'YYYY-MM-DD')`,
    toIsoDate(startDate),
  );
}

Validate the finished query

Before execution, especially while debugging, log the SQL and non-sensitive parameter metadata:

log.debug({
  title: 'SuiteQL query',
  details: {
    sql,
    parameterCount: params.length,
    parameterTypes: params.map((value) => typeof value),
  },
});

Avoid logging personal data, secrets or whole production payloads.

A useful invariant is simple: the number of placeholders in the completed SQL must equal the number of parameter values, and their order must describe the same conditions. Building both arrays together makes that invariant much harder to break.