When running a modern version of the SQL server, such as 2019, you can run queries with lots of parameters. For example I used an IN expression with 30000 IDs, which the server executed successfully. While there are cases where you run into other limits, such as the "qurey too complex" error, this has nothing to do with parameter count limiting specifically.
As far as I understand, this limit is a relic of the past and probably required for backwards compatibility. However, can't we detect that we are running a newer version of the SQLServer and disable the limit or increase it heavily?
For testing, I directly executed this query with sqlcmd:
I've added a file with SQL to create and select 10000 entries to this issue.
As far as the microsoft docs of the IN expression go, there is no mention of this:
However, a not further specified limit due to high resource usage is mentioned:
I don't know the state of this in older versions of SQL Server versions, so everything I am saying here is in regard of the SQL Server 2019.
SQL Server 2019