The Incoming Request Has Too Many Parameters. the Server Supports a Maximum of 2100 Parameters

The incoming request has too many parameters. The server supports a maximum of 2100 parameters

Simple - as long as TheTAbleID's contains less than 2100 ID's then - it is not legal to do that.

Cut the table into blocks of 2000 and then query each block separately, possibly in multiple threads.

The incoming request has too many parameters. The server supports a maximum of 2100 parameters-sql2008/2012

My advice would be to use a table-valued parameter. Here's an example in ad hoc T-SQL:

--Create the type only once in your database
create type myTableType as table (a int)

--This happens every time
declare @a myTableType;

insert into @a (a) values (1), (2), (3);

select * from someTable
where id in (select a from @a)

Once you've created a table type in your database, you can have parameters of that type (in my example myTableType) passed to your stored procedure. Keep in mind that they behave for all intents and purposes like a table variable.

Where In Clause error on The server support a maximum of 2100 parameters only

There are many ways to solve this problem.

Instead of sending a list of IDs as seperate parameters, you can send a single @IDList parameter as a single comma separated string and let it parsed into IDs at the server side. Here is a function that I use for this (borrowed and modified from Jeff Moden's code):

CREATE FUNCTION [dbo].[iSplitter] (@Parameter VARCHAR(MAX))
RETURNS @splitResult TABLE (number INT, [value] INT)
AS
BEGIN
SET @Parameter = ','+@Parameter +',';

WITH cteTally AS
(
SELECT TOP (LEN(@Parameter))
ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
FROM Master.sys.All_Columns t1
CROSS JOIN Master.sys.All_Columns t2
)
INSERT @splitResult
SELECT ROW_NUMBER() OVER (ORDER BY N) AS Number,
SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1) AS [Value]
FROM cteTally
WHERE N < LEN(@Parameter) AND SUBSTRING(@Parameter,N,1) = ','
RETURN
END

With this function created once, I do it like:

sqlDBComm.CommandText = @"SELECT * FROM TIME_ATTENDANCE_REPORT tar
inner Join dbo.iSplitter(@UserIdList) ul on tar.USERID = ul.[value]
WHERE TRXDATETIME = @Date
ORDER BY USERID ASC ";
sqlDBComm.Parameters.AddWithValue("@UserIdList",string.Join(",", userIDList));

This works very well for 5-6K integer ids but times out if used with 20-30K or more IDs. Then I created another alternative as a CLR procedure, and that one parses the list server side in less than a second. But I think this one is sufficient for your needs.

Another way is to send the IDs as an XML parameter and parse server side again.

Yet another way is to send a table parameter.

PS: Here is a link that shows sample code for other ways. The site is in Turkish but the codes are crystal clear in C#, separate per approach.

EDIT: XML sample using Northwind Orders table:

void Main()
{
int[] IDList = { 10265,10266,10267,10268,10269,10270,10271,10272,10273,10274,10275, 10320, 10400 };
var idsAsXML = new XElement("IDS",
from i in IDList
select new XElement("Row", new XAttribute("Id", i)));

string sql = @"
DECLARE @hDoc int;
DECLARE @tbl TABLE (Id int);
exec sp_xml_preparedocument @hDoc OUTPUT, @XML;
INSERT @tbl
SELECT *
FROM OPENXML(@hDoc, @Nodename, 1) WITH (Id int);
EXEC sp_xml_removedocument @hDoc;

select * from Orders o
where exists (select * from @tbl t where t.Id = o.OrderId) ";

DataTable tbl = new DataTable();
using (SqlConnection con = new SqlConnection(@"server=.\SQLExpress;Trusted_Connection=yes;Database=Northwind"))
{
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddWithValue("@XML", idsAsXML.ToString());
cmd.Parameters.AddWithValue("@NodeName", "/IDS/Row");
con.Open();
tbl.Load(cmd.ExecuteReader());
con.Close();
}

//tbl.Dump(); // linqPad luxury
}


Related Topics



Leave a reply



Submit