3

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.

Rob
  • 45,296
  • 24
  • 122
  • 150
Md. Md Mamun Ar Rashid
  • 90
  • 1
  • 7
  • 8
    You do not want to add more than 10000 parameters to an SQL command. – Matti Virkkunen Jan 31 '11 at 09:31
  • 1
    http://msdn.microsoft.com/en-us/library/ms143432.aspx and search parameters keyword :) – Serkan Hekimoglu Jan 31 '11 at 09:36
  • 2
    We passed WTF City riding straight to Helltown. No, really, why? Maybe there is a better solution than using 10k parameters? – SWeko Jan 31 '11 at 09:36
  • Hehe, this has been asked before. See: http://stackoverflow.com/questions/567963/linq-expression-to-return-property-value/568771#568771 – Jimmy Chandra Jan 31 '11 at 09:37
  • 1
    The fact that this question has two upvotes makes me think that I am doing question voting wrong. – AakashM Jan 31 '11 at 19:24
  • I have to vote this question up JUST because I needed a laugh tongight – David Feb 01 '11 at 05:22
  • 1
    @AakashM: The fact that the OP's solution is to his problem is a bad idea does not mean his question is a bad question. If nothing else, it is somewhat educational since answering it involves a discussion of useful stuff like `SqlBulkCopy`. – Brian Feb 01 '11 at 21:20

2 Answers2

13

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.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • 12
    The limit is 2100, because at 2101, the Shub-SQLurath will be summoned, and darkness will engulf the earth, as foretold in the Databasenomicron. – SWeko Jan 31 '11 at 09:43
  • I am generating insert query and 10000 parameter from 10000 objects.I want to execute this query by a single command.Have any way to execute this query by a single round trip to Database? – Md. Md Mamun Ar Rashid Jan 31 '11 at 10:02
  • 1
    @Engr - no, that simply isn't the way to do it. Break it into multiple commands, or use any of the tricks I mentioned above. For inserts, table-valued parameters is pretty much **designed** for this scenario - why not use them? – Marc Gravell Jan 31 '11 at 10:21
  • @Engr Why not just insert each object one after the other in a transaction instead? Makes things simple that way – Iain Ward Jan 31 '11 at 10:22
  • 1
    @w69rdy round trips start to hurt you at that volume – Marc Gravell Jan 31 '11 at 10:26
  • 4
    @w69rdy plus: anyone opens a transaction spanning 10k round-trips on one of my systems is going to get shot – Marc Gravell Jan 31 '11 at 10:26
  • `SqlBulkCopy` *without* a staging table is also a reasonable way to perform a bulk insert, if the data doesn't need to be modified on the server before being inserted. – Brian Feb 01 '11 at 16:56
  • @Brian that depends a bit on your transaction log configuration. Bulk inserts can be non-logged – Marc Gravell Feb 01 '11 at 18:17
3

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.

dav_i
  • 27,509
  • 17
  • 104
  • 136
leppie
  • 115,091
  • 17
  • 196
  • 297