sqlserver中where条件in的参数个数最大为2100个。批量处理代码落地改造。

sqlserver中where条件in的参数个数最大为多少个

在SQL Server中，WHERE条件中IN子句的参数个数最大为2100个。这是由于SQL Server中IN子句的参数列表是由一个表达式列表构成的，而每个表达式列表的长度不能超过2100个。如果需要使用更多的参数，可以考虑使用临时表或者其他方法来实现。 

国内AI全挂

代码改造

/// <summary>
        ///  批量更新成员级别 
        /// </summary>
        /// <param name="tran"></param>
        /// <param name="model"></param>
        private void Updatelevels(SqlTransaction tran, RouterolememberBM model,string[] ids)
        {
            string sql = @"  
UPDATE routerolemember  
 Set   cnvcmemberlevel=@cnvcmemberlevel,cnvcmemberleveltext=@cnvcmemberleveltext,
       cnvcupdateuser=@cnvcupdateuser,
       cndupdatedate=@cndupdatedate 
 from routerolemember
inner join (select	T.c.value('.', 'varchar(50)') as PersonCode  from @IDsXml.nodes('/Array/Item') T (c)) as T 
on routerolemember.cniId=T.PersonCode ";
            List<SqlParameter> list = new List<SqlParameter>();
            //list.Add(CreateParameter("@cniId", SqlDbType.Int, 4, model.Id)); 
            list.Add(CreateParameter("@cnvcmemberlevel", SqlDbType.VarChar, 200, model.Memberlevel));
            list.Add(CreateParameter("@cnvcmemberleveltext", SqlDbType.VarChar, 200, model.Memberleveltext));
            list.Add(CreateParameter("@IDsXml", SqlDbType.Xml, 7000, this.CheckPersonCodes(ids)));  
            list.Add(CreateParameter("@cnvcUpdateUser", SqlDbType.VarChar, 200, model.Updateuser));
            list.Add(CreateParameter("@cndUpdateDate", SqlDbType.DateTime, 200, DateTime.Now));
            bool result = SqlHelper.ExecuteNonQuery(tran.Connection, tran, CommandType.Text, sql, list.ToArray()) > 0;
        }

  /// <summary>
        /// xml by cao919 20230605
        /// </summary>
        /// <param name="pCodes"></param>
        /// <returns></returns>
        private string CheckPersonCodes(IEnumerable<string> pCodes)
        { 
            var personCodesXml = new StringBuilder(); 
            personCodesXml.Append(@"<Array>");
            foreach (var pCode in pCodes)
            {
                personCodesXml.AppendFormat(@"<Item>{0}</Item>", pCode);
            }
            personCodesXml.Append(@"</Array>");

            return personCodesXml.ToString();
        }

.ids.Split(',').ToArray()