Too many parameters were provided in this RPC requ

Question

A search query returned this error. I have a feeling its because the in clause is ginormous on a subordinant object, when I'm trying to ORM the other object.

Apparently in clauses shouldn't be built 1 parameter at a time. Thanks ibatis.

Answer 1

Your best bet is to revise your application to pass less than 2100 parameters to the stored procedure. This is a DBMS limit that can't be raised.

Answer 2

I got this same error when using an apparently innocent LINQ to SQL query. I just wanted to retrieve all the records whose ids were amongst the ones stored in an array:

dataContext.MyTable.Where(item => ids.Contains(item.Id)).ToArray();

It turned out that the ids array had more than 2100 items, and it seems that the DataContext adds one parameter for each item in the array in the resulting SQL query.

At the end it was a bug in my code, since the ids array had not to have so many items. But anyway it is worth to keep in mind that some extra validation is needed when using such constructs in LINQ to SQL.

Answer 3

You can do a few things:

1. Pump the params into a temp table and use said temp table to filter your query. See https://stackoverflow.com/a/9947259/37055
2. Create a comma-delimited array, and pass the array into SQL Server as a varchar(x). Split it out via TSQL (here are a few methods) and use the resulting rowset to filter your search results.
3. Have a look at your application logic. It's more than a little strange to be passing 2100 parameters to a stored procedure.

Answer 4

If you are passing 2100 parameters to a single stored procedure, you are simply doing something wrong. Don't raise the limit or try to work around this abomination, figure out how to do things right.