ansaurus

Question

Can I set "Parameters per stored procedure" higher than 2100 in SQL Server 2005/08?

Answer 1

+3 A:

It's a hard limit - and I would seriously think about rethinking a real need to extend that, especially in light of the example code you give.

Cade Roux 2009-01-29 16:27:15

Interesting comment... are you saying that you don't think the ".Contains" method should be used? If so, what alternative would you suggest for allowing people to call some sort of method (lets call it "ExtractPhoneNumbers") with an arbitrary amount of selected accounts.

Timothy Khouri 2009-01-29 17:12:16

Nothing is ever arbitrary, at some point, most design choices break down. When you approach collections that large, I believe linqtosql handles them differently. But regardless, in any situation like that, I think you might have to work outside any framework where the implementation cannot scale.

Cade Roux 2009-01-29 17:32:22

In other words, at some point the usefulness of any abstraction may break down. Again, I think LINQ2SQL actually has an internal mechanism to switch to another technique to make large contains more effective.

Cade Roux 2009-01-29 17:34:17

Answer 2

+3 A:

There is no way to increase that number. You could put the values you need in another table (or tables), insert the records into that table, with a key, and then pass that key to the stored procedure.

casperOne 2009-01-29 16:28:18

I appreciate the thought, but I wouldn't want to mucky up every usage of the ".Contains" method in LINQ to store things in a temp table. - TY for the answer about the limit.

Timothy Khouri 2009-01-29 17:09:15

Answer 3

A:

I'm aiming for a more long term and permanent solution to this using xml. For SQL Server 2005 and above an xml type could probably be used to bring over a local collection in Linq to SQL as a single parameter and maybe OpenXml could be used for SQL Server 2000.

As far as being able to increase that limit, I'm not sure and that's how I actually ended up at this post. I'll post back anything I find here though after my research.

See my post on the Microsoft Connect site and vote if you would also like to see this fixed.

https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=475984

jpierson 2009-07-23 12:06:42

Unfortunately it looks like Microsoft has confirmed the issue and has rejected the suggested fix for this. BTW, it appears that in the DBLinq project that they are using CSV somehow to convey these multi-value parameters.

jpierson 2009-07-30 18:09:16

ansaurus

tags:

views:

answers:

Can I set "Parameters per stored procedure" higher than 2100 in SQL Server 2005/08?

related questions