存储过程接收JSON格式数据

前端有可能一次性上传多笔记录，并使用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方法来进行。

存储过程接收JSON格式数据

相关推荐