能用批量操作时,就不要一条一条处理,减少与数据库连接次数及快速更新数据。 可用xml数据类型传参。优点:用C#拼XML,用SQL解析XML到变量或临时表,再批量处理,可提高系统效率。使用时注意事务不要过大
XML传参数示例
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
CREATE PROC dbo.UP_Test_XmlPara @SeqNoList XML AS SET NOCOUNT ON DECLARE @TableSeqNo TABLE ( SeqNo INT ) INSERT INTO @TableSeqNo(SeqNo) SELECT SeqNo=T.c.value('./text()[1]', 'INT') FROM @SeqNoList.nodes('/SeqNos/seqno') AS T(c) SELECT * FROM dbo.NCC_TB_AgentRecord A INNER JOIN @TableSeqNo B ON A.SeqNo = B.SeqNo GO --test DECLARE @SeqNoList XML SET @SeqNoList = ' <SeqNos> <seqno><![CDATA[93]]></seqno> <seqno>165794</seqno> </SeqNos> ' EXEC dbo.UP_Test_XmlPara @SeqNoList = @SeqNoList DROP PROC dbo.UP_Test_XmlPara