526互联

sqlsever解决传入参数过多的一种思路

发布时间 2023-05-04 09:01:32作者: yoyoaa

1.问题

　　com.microsoft.sqlserver.jdbc.SQLServerException: 传入的请求具有过多的参数。该服务器支持最多 2100 个参数。请减少参数的数目，然后重新发送该请求。

2.环境

　　java + sqlsever 或  sqlsever存储过程

3.java + sqlsever 解决方案

　　3.1 将数据处理成XML格式

if (!StringUtil.isEmpty(dto.getFareaID())) {
    String[] split = dto.getFareaID().split(",");
    String str = "<root>";
    for (String areaStr : split) {
        str = str + "<Item>" + areaStr + "</Item>";
    }
    str = str + "</root>";
    dto.setFareaID(str);
} else {
    dto.setFareaID(null);
}

　　3.2 查询

SELECT mpb.FBatchNo,mbrn.BatchNumber,mpb.FUnitNames
FROM dbo.MY_PigBatch AS mpb
LEFT JOIN dbo.MY_BatchRecordNo AS mbrn ON mbrn.BatchNO=mpb.FBatchNo AND mbrn.CheckFlag=1
<where>
    AND mpb.FSegmentID=13907
        <if test="dto.fbatchNoSate != null and dto.fbatchNoSate != ''">
            and mpb.FState = #{dto.fbatchNoSate}
        </if>
        <if test="dto.fareaID != null and dto.fareaID != ''">
            and CAST(#{dto.fareaID} as xml).exist('/root/Item[text()=sql:column("mpb.FAreaID")]')=1
        </if>
        <if test="dto.ffieldID != null and dto.ffieldID != ''">
            and CAST(#{dto.ffieldID} as xml).exist('/root/Item[text()=sql:column("mpb.FFieldID")]')=1
        </if>
</where>

4.sqlsever　存储过程解决方案

ALTER PROCEDURE [存储过程名]
    @AreaID XML
AS
BEGIN
    SET NOCOUNT ON;

    select T.c.query('.').value('(Item)[1]','int') as id INTO #tmpArea from @AreaID.nodes('/root/Item') as  T(c)

    select count(1) from table as a
      where @AreaID IS NULL OR  EXISTS(SELECT 1 FROM #tmpArea Area WHERE Area.id=a.FAreaID)
END

5.sql查询例子

select top 100 * from MY_PigBatch mpb
where  CAST('<root><Item>13902</Item><Item>13903</Item></root>' as xml)
            .exist('/root/Item[text()=sql:column("mpb.FAreaID")]')=1