SQL query not taking values more than 2100

Question

SELECT MachineID, MachineName, count(ID) as Total, sum(Size) as TotalSize 
  FROM Files 
  join Machines on Files.MachineID = Machines.MachineID 
 Where Files.MachineID In(sql.Append(string.Format("@MachineId{0}", i));
 group by Files.MachineID,MachineName

now when the machinId count is less than 2100 the query is performed and if it machines go above 2100 an error is thrown Error:

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100.

how do i make increase the limit or just avoid getting this error.. and put values in gridview thanks..

Answer 1

You don't say in your example where your "sql" variable comes from but if you manually build your 'IN' list (by building a string with comma delimited values in your IN statement), then all popular relational DBs have a limit to how many values you can specify in a static IN clause. The database your are using has a 2100 limit. I believe Oracle is 1000

Answer 2

you could use string functions. In SQL Server your WHERE clause would be something like

...WHERE CHARINDEX(':' + <: delimited list of machine IDs> + ':', 
       ':' + CAST(Files.MachineID as VARCHAR(10)) + ':') <> 0

The : delimiters are necessary to prevent 100 from matching with 1001, 1002, 2100, etc. A sample query string would be

...WHERE CHARINDEX(':1000:1001:1002:1005:', 
       ':' + CAST(Files.MachineID as VARCHAR(10)) + ':') <> 0