No more soup for you! - Cool SQL Error


I had situation, where we
wanted to see how the
SQL Server can deal
with high number of
items in the IN clause,
For instance, we had
something like this in a SP:



SELECT  Firstname
FROM    dbo.Customers

WHERE  CustomerID IN (3243,32432,324564,45645,767,....xxxx)

The number of CustomerIDs in the IN clause about 50,000.
And they were passed as input parameter.

When we tried to execute the SP, we got the following error:

Error: 8623, Severity: 16, State: 1 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 or queries that reference a very large
number of tables or partitions. Please simplify the query. If you believe you
have received this message in error, contact Customer Support Services for
more information.

It seems, that the SQL Server had a persoalan to compile the SP!