RSS Feed

Method R Discussion Group

We have a middle tier application that creates dynamic SQL statements based on server-side templates that we develop.

We recently converted our server-side app to use bind variables and parse once/execute many times, but we ran into issues in applying bind variables to a WHERE clause component that uses an IN list in which the listed values can vary.

Our current versions not use bind variables for the IN lists. We are wondering if there are any best practices related to this need?

It depends on the cardinality of list for your IN list operator.

If it varies from 1-128, then bind null values to the ones you don't need for a given execution (assuming that a null value is not a valid value which it probably isn't).

If it varies above 128, then I would seriously consider redesigning the application/schema so that you can perform a natural join rather than using an IN list operator.