Is there any limit for IN results in SQL Server?

Is there any limit for the content that the IN filter can handle? For example:

SELECT Name
FROM People
WHERE Job IN (All the values goes here)

Microsoft docs for IN says:


"Explicitly including an extremely large number of values (many
thousands of values separated by commas) within the parentheses, in an
IN clause can consume resources and return errors 8623 or 8632. To
work around this problem, store the items in the IN list in a table,
and use a SELECT subquery within an IN clause."

but is there any exact or approximate number for


many thousands of values

It depends. Seriously. That’s why the docs can’t be specific about where you will notice degradation in your environment.

The solution is to just stop doing this (and worrying about it) and use a table-valued parameter.

If you have the values in C# in such a way that you can build a comma-separated list as a string to concatenate together in a query, you have the values in C# in such a way that you could stuff them in a DataTable or other structure (maybe that's where they're coming from in the first place), and pass that structure as a parameter to the stored procedure.

StackExchange Database Administrators Q#228695, answer score: 17