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

 

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

表如下:

 

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


准备存储过程:

 

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格式。

参考《符号分割的字符串转换为XMLhttps://www.cnblogs.com/insus/p/10928041.html

 

举个例子:

 

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

 

posted @ 2019-05-26 23:13 Insus.NET 阅读(...) 评论(...) 编辑 收藏