欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

存储过程接收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方法来进行。