逗号分割字符串经存储过程存入数据表中

ゝ一纸荒年。 2021-11-22 09:02 315阅读 0赞

前端提交一串逗号分割的字符串，经存储过程，存入SQL数据表中。

表如下：

[![28036-20190526230417622-1274866456.png][]][28036-20190526230417622-1274866456.png]

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

CREATE TABLE [dbo].[Miscellaneous]
    ( 
         [ID] INT IDENTITY(1,1) NOT NULL,    
         [Item] NVARCHAR(50) NOT NULL DEFAULT(N'')
    )
    GO

Source Code

准备存储过程：

[![28036-20190526230702243-303351491.png][]][28036-20190526230702243-303351491.png]

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

CREATE PROCEDURE [dbo].[usp_Miscellaneous_Insert]
    (
        @comma_delimited_string NVARCHAR(MAX)
    )
    AS
    BEGIN
        DECLARE @xml XML = [dbo].[svf_ConvertToXML](@comma_delimited_string)
    
        INSERT INTO  [dbo].[Miscellaneous](Item)
        SELECT nref.value('.','NVARCHAR(MAX)') AS [Item]
        FROM @xml.nodes('/insus') AS R(nref)
    END
    
    GO

Source Code

上面\#43行代码，有一个自定义函数，它是把逗号分割的字符串转为XML格式。

参考《[符号分割的字符串转换为XML][XML]》[https://www.cnblogs.com/insus/p/10928041.html][XML]

举个例子：

[![28036-20190526231012150-2103217879.png][]][28036-20190526231012150-2103217879.png]

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

DECLARE @str NVARCHAR(MAX) = N'金，水，木，火，土'
    EXECUTE [dbo].[usp_Miscellaneous_Insert] @str
    SELECT [ID],[Item] FROM [dbo].[Miscellaneous]

Source Code

转载于:https://www.cnblogs.com/insus/p/10928264.html

[28036-20190526230417622-1274866456.png]: /images/20211122/243e9c499e504449ba7fd013b75806ca.png
[ContractedBlock.gif]: https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif
[ExpandedBlockStart.gif]: /images/20211122/c6071fc769594ff48e0653c0475737fc.png
[28036-20190526230702243-303351491.png]: /images/20211122/c70f117ab3cb4f749c9cd4350b991f09.png
[XML]: https://www.cnblogs.com/insus/p/10928041.html
[28036-20190526231012150-2103217879.png]: /images/20211122/4d861119e552413ca7ee73a798895961.png