XML传参数示例

能用批量操作时，就不要一条一条处理，减少与数据库连接次数及快速更新数据。可用xml数据类型传参。优点：用C#拼XML，用SQL解析XML到变量或临时表，再批量处理，可提高系统效率。使用时注意事务不要过大

XML传参数示例

View Code

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