存储过程接收JSON格式数据
程序员文章站
2022-04-15 15:41:42
前端有可能一次性上传多笔记录,并使用JSON序列化。 现在在MS SQL Server 2016版本上,可以直接处理JSO数据。 如下面的前端序列化的数据: DECLARE @json_string NVARCHAR(MAX) = N' { "catalog":[ {"ID":23394,"Item ......
前端有可能一次性上传多笔记录,并使用json序列化。
现在在ms sql server 2016版本上,可以直接处理jso数据。
如下面的前端序列化的数据:
declare @json_string nvarchar(max) = n' { "catalog":[ {"id":23394,"item":"i32-gg443-qt0098-0001","category":"s","qty":423.65}, {"id":45008,"item":"i38-aa321-ws0098-0506","category":"b","qty":470.87}, {"id":14350,"item":"k38-12321-5456ud-3493","category":"b","qty":200.28}, {"id":64582,"item":"872-rtde3-q459pw-2323","category":"t","qty":452.44}, {"id":23545,"item":"098-ssss1-ws0098-5526","category":"s","qty":500.00}, {"id":80075,"item":"b78-f1h2y-5456ud-2530","category":"t","qty":115.06}, {"id":53567,"item":"po0-7g7g7-jjy098-0077","category":"q","qty":871.33}, {"id":44349,"item":"54f-art43-6545nn-2514","category":"s","qty":934.39}, {"id":36574,"item":"x3c-sdewe-3er808-8764","category":"q","qty":607.88}, {"id":36574,"item":"rvc-43ase-h43qww-9753","category":"u","qty":555.19}, {"id":14350,"item":"k38-12321-5456ud-3493","category":"b","qty":200.28}, {"id":64582,"item":"872-rtde3-q459pw-2323","category":"t","qty":452.44}, {"id":80075,"item":"b78-f1h2y-5456ud-2530","category":"t","qty":115.06}, {"id":53567,"item":"po0-7g7g7-jjy098-0077","category":"q","qty":871.33}, {"id":44349,"item":"54f-art43-6545nn-2514","category":"s","qty":934.39}, {"id":44349,"item":"54f-art43-6545nn-2514","category":"s","qty":934.39}, {"id":36574,"item":"x3c-sdewe-3er808-8764","category":"q","qty":607.88}] } '
在数据库中,创建一张表来存储这些数据:
create table [dbo].[parts catalog] ( [id] int, [item] nvarchar(40), [category] nvarchar(25), [qty] decimal(18,2) ) go
创建存储过程来接收并处理json数据:
create procedure [dbo].[usp_parts_catalog_insert] ( @json_string nvarchar(max) ) as insert into [dbo].[parts catalog]([id],[item],[category],[qty]) select [id],[item],[category],[qty] from openjson(@json_string,'$.catalog') with ( [id] int '$.id', [item] nvarchar(40) '$.item', [category] nvarchar(25) '$.category', [qty] decimal(18,2) '$.qty' ) go
执行存储过程,并查询表数据:
以前上传多笔记录,均是使用表函数来处理,现在可以使用openjson方法来进行。