CODEWITHSUNDEEP
c#
Md. Md Mamun Ar Rashid
Md. Md Mamun Ar Rashid

Reputation: 90

How to add more than 10000 parameters to a single sql command

I want to add more than 10000 parameters to a single sql command. How it possible? As far i know 2100 parameters a single sql command.

Upvotes: 3

Views: 2998

Answers (2)

leppie
leppie

Reputation: 117220

You can't. SQL Server supports a maximum of 2100 parameters.

Your only option is to generate a SQL string containing all those parameter values.

Incidentally, SQL CE does not have this limitation.

Upvotes: 3

Marc Gravell
Marc Gravell

Reputation: 1062770

I think you step back to the "why" rather than jumping right to the "how". It sounds like maybe you are doing some huge insert/update, in which case a more appropriate solution would be table-valued-parameters.

Other options include:

  • passing in xml and using SQL server to parse the xml (it is quite good at this)
  • loading data first with something like SqlBulkCopy into a staging table, then executing a command (raw TSQL or a SPROC) to push the data from the staging table into the transactional tables
  • if it is just a list of ids, write a "split" function (udf) at the server and pass in a [n]varchar(max)
  • batch the operations into a number of more sane commands

You really don't want to do something with 10k parameters on one command; that way madness lies.

Upvotes: 13

Related Questions