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

Question

I'm currently trying to try catch the insert SQL query from my simple C# application, but when i'm trying to insert the data into database with user id which is added into userIDList parameter, the error came out saying that

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

The userIDList sometimes will contains like 60 arrays above then the error will popped out.

My SQL CommandText will contain of

"SELECT * FROM TIME_ATTENDANCE_REPORT WHERE TRXDATETIME = @Date AND USERID IN (001,002,003,004,....)

So i think if more then certain number then the error popped out

Here are my sample code :

List<string> userIDList = new List<string>();
using (SqlCommand sqlDBComm = new SqlCommand())
                    {
                        openConnection();
                        SqlDataReader sqlDBReader;
                        sqlDBReader = null;
                        sqlDBComm.CommandText = "SELECT * FROM TIME_ATTENDANCE_REPORT WHERE TRXDATETIME = @Date AND USERID IN (" + string.Join(",", userIDList) + ") ORDER BY USERID ASC ";
                        sqlDBComm.Parameters.Add("@Date", SqlDbType.DateTime);
                        sqlDBComm.Parameters["@Date"].Value = GetDateFrom;
                        sqlDBComm.Connection = sqlDB;
                        sqlDBComm.CommandType = CommandType.Text;
                        try
                        {
                            sqlDBReader = sqlDBComm.ExecuteReader();
                            t.Load(sqlDBReader);
                            sqlDBReader.Close();

                            if (t.Rows.Count > 0)
                            {
                                status = "Update";
                            }
                            else
                            {
                                status = "Insert";
                            }
                        }
                        catch (Exception errMsg)
                        {
                            MessageBox.Show("Error Code: " + errMsg.ToString());
                        }
                        finally
                        {
                            sqlDBReader.Close();
                            closeConnection();
                        }
                    }

Any other solution can resolve this? Thanks

Answer 1

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
}

Answer 2

You can create a Table-Valued-Parameter and pass it as a parameter. It requires you to create a new type in your database, and will enable you to pass an array to the query and let the database treat it as a table. If this is something you do a lot, it could come in handy.

I no longer have access to the project where I implemented this but everything is available in the blog post. The code below is not tested, but I hope it can get you in the right direction.

1. Create a new type in your database:

CREATE TYPE integer_list_tbltype AS TABLE (n int NOT NULL PRIMARY KEY)

2. Pass it as parameter:

sqlDBComm.Parameters.Add("@userIds", SqlDbType.Structured)
sqlDBComm.Parameters["@userIds"].Direction = ParameterDirection.Input
sqlDBComm.Parameters["@userIds"].TypeName = "integer_list_tbltype"
sqlDBComm.Parameters["@userIds"].Value = CreateDataTable(userIDList)

3. Method for creating the parameter:

private static DataTable CreateDataTable(IEnumerable<int> ids) {
    DataTable table = new DataTable();
    table.Columns.Add("n", typeof(int));
    foreach (int id in ids) {
        table.Rows.Add(id);
    }
    return table;
}

4. Use it in your SQL:

... AND USERID IN (SELECT n FROM @userIds)

CreateDataTable from here:

How to pass table value parameters to stored procedure from .net code

Rest from here:

http://www.sommarskog.se/arrays-in-sql-2008.html#introduction