Give a customer enough flexibility and eventually they’ll try to pass a few hundred values into a report parameter. And when that happens, and the query dies a fiery death, they’ll ask the same thing every time: “Why did this work before?”

Spoiler: it didn’t. Not reliably.

This post is about a real incident where a large enterprise customer attempted to run a standard SSRS report with a parameter set so large it crashed SQL Server’s query optimiser. What followed was a journey through query complexity, expectations management, and one delightfully effective workaround.

Error 8623: A Rare Event (Apparently)

The error was as follows:

The query processor ran out of internal resources and could not produce a query plan. 
This is a rare event and only expected for extremely complex queries...

The culprit? A dynamic IN clause with over 300 Items. Once that number crept past ~281, the report would fail to render.

This wasn’t an SSRS bug. This was SQL Server doing what SQL Server does when you ask it to juggle flaming swords: it throws them back at you.

The Internals: Why SQL Server Chokes

When you write a query like:

SELECT *
FROM TableName
WHERE Item IN ('A1', 'A2', ..., 'A309')

SQL Server internally expands that to:

WHERE Item = 'A1' OR Item = 'A2' OR ... OR Item = 'A309'

That many predicates can quickly overwhelm the optimiser. It tries, bless it, but eventually throws Error 8623 (or its cousin Error 8632 if you really push it).

The Fix: A String Splitter and a Temp Table

Instead of passing a list of values into an IN clause, we switched to passing a single comma-separated string and using a string splitter function. Ours looked something like this:

SELECT *
FROM TableName t
JOIN dbo.fn_SplitString(@ItemList, ',') s ON t.Item = s.Value

We also inserted the split result into a temp table, applied a clustered index, and joined from there. The SQL plan was leaner, more predictable, and crucially—successful.

We even added a DISTINCT before the insert to reduce noise, especially if the calling application allowed duplicates.

But Wait, There’s a Limit

Yes, even this has caveats:

  • The string passed in must be under 8000 characters.
  • The approach works best on systems where you can modify the report query directly (e.g., Portal reports).

Still, it’s far more stable than hundreds of OR conditions.

What the Customer Expected

Naturally, the response was:

"But it used to work. We don’t want to run smaller parameter sets."

Which, fair. But also: no, it didn’t work. Not reliably. And SQL Server wasn’t designed to handle dynamic IN clauses with that many terms in a reporting context.

We provided a working fix that didn’t require splitting the report or compromising on functionality. From the customer’s perspective, nothing about their workflow changed—the report still accepted the same parameters and returned the same data. All the changes were behind the scenes.

Final Thoughts

Flexible systems invite creative misuse. That’s not a bad thing, but it does mean the architecture needs to account for edge cases. SQL Server has a limit to how much complexity it can handle in a single predicate. And in this case, that limit was somewhere around “a few hundred Items”.

We didn’t redesign the report engine. We didn’t overhaul the customer’s data warehouse. We added a little string splitting function and it made everything better.

Sometimes, that’s all it takes.

Share on: