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

思路"/>

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

问题

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

方案

　　java + sqlsever 或  sqlsever存储过程

方案1 java + sqlsever 解决方案

　　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);
}

         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>

方案2 存储过程解决方案

ALTER PROCEDURE [存储过程名]@AreaID XML
AS
BEGINSET 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 awhere @AreaID IS NULL OR  EXISTS(SELECT 1 FROM #tmpArea Area WHERE Area.id=a.FAreaID)

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