Framework: Illuminate\Database\QueryException with message 'SQLSTATE[IMSSP]: Tried to bind parameter number 2101. SQL Server supports a maximum of 2100 parameters.

[xandrw]

• Laravel Version: 5.4.*
• PHP Version: 5.6.30
• Database Driver & Version: Microsoft SQL Server 2012 (11.0.2100.60) (sqlsrv)

Description:

It seems like Laravel is not covering the case where the number of WHERE [column] IN parameters exceeds 2100 in Microsoft SQL Server, giving the following exception:

Illuminate\Database\QueryException with message 'SQLSTATE[IMSSP]: Tried to bind parameter number 2101. SQL Server supports a maximum of 2100 parameters.

Bear in mind that the 2100 limit is hard coded, it cannot be changed, others might have this lower or higher.

I propose that the whereIn() method should count the number of parameters and if it exceeds 1000, the parameters should be divided and whereIn() would be called again internally on each divided parameter set.

This way, the resulting query would be something like ... WHERE [column] IN (1, 2, 3, ..., 1000) AND WHERE [column] IN (1001, 1002, 1003, ..., 2000) ... and so on.

Steps To Reproduce:

Seeing that every Laravel installation comes with the App\User model by default, make sure you're running on a sqlsrv connection, run the default migrations and try the following:

$parameters = [];

for ($i = 0; $i <= 2101; $i++) $parameters[] = $i;

App\User::whereIn('id', $parameters)->get();

[xandrw]

Just to clarify, I am not using this approach directly, this happens behind the scenes when eager loading a relation is done, that relation contains thousands of entries.

[themsaid]

5.4 isn't supported anymore, please upgrade.

[staudenmeir]

The problem occurs on all Laravel versions.

[xandrw]

As @staudenmeir mentioned, this happens on all Laravel versions, tested on 5.5 and got the same result, please don't rush to close Issues like this @themsaid.

[staudenmeir]

Since this probably doesn't affect a lot of people, I doubt that it will be fixed in the core (#4389).

I created a package: https://github.com/staudenmeir/eloquent-param-limit-fix

[Chris25602]

I am on php 7.3 and laravel 5.5 this occurs everyday. I essentially code around it doing exactly what the OP suggests. The code would probably be faster if this case were covered by the actual pdo_sqlsrv team not laravel. My guess though is this will be out of scope for any team. this really just application logic.