Handling SQL "IN" criteria lists

We recommend that you use SQL JOINs in favor of hard-coded IN lists whenever possible.

Both SQL Server and Oracle can only handle so many criteria values in a SQL IN statement. Oracle can handle about 100 entries while SQL Server can handle a few thousand. You can re-run a SQL multiple times passing in a batch of values to the IN list. Then your code merges those results together, if needed. This code demonstrates how you can parse a large list of IN criteria values:

Handling SQL In statements
Note: 
  • The highlighted yellow value indicates the size of the batches of criteria that this code will work with.
  • The green highlighted code breaks down your large list of criteria values into smaller batches.
  • The Do While loop iterates over the green code.