创建一个存储过程，该过程在读取JSON字符串后会提取数据 [英] Creating a stored procedure which ingests data after reading a JSON string

问题描述

我想在SQL Server中创建一个存储过程，该存储过程可以接受JSON数据并将其上载到指定表的相关列中.例如，使用以下JSON字符串:

I would like to create a stored procedure, in SQL Server, that can accept JSON data and upload it into a specified table into its relevant columns. For instance, take this JSON string:

[{"ID":5,"LastName":"Donalds","FirstName":"Dave","Age":23,"City":"London"}]

上面的字符串可以通过以下查询生成:

This above string can be produced with the following query:

SELECT 
5 as 'ID',
'Donalds' as 'LastName',
'Dave' as 'FirstName',
23 as 'Age',
'London' as 'City'
FOR JSON PATH

我设法编写了一个脚本，该脚本可以上传到我的表"Persons"中.我的表具有以下CREATE脚本:

I've managed to write a script which can upload into my table called 'Persons'. My table has the following CREATE script:

CREATE TABLE [dbo].[Persons](
    [ID] [int] NOT NULL,
    [LastName] [varchar](255) NOT NULL,
    [FirstName] [varchar](255) NULL,
    [Age] [int] NULL,
    [City] [varchar](255) NULL
) ON [PRIMARY]
GO

我设法编写了一个脚本，可以将上述JSON上传到我的表中，但是我想在存储过程中进一步使其自动化.上传上述JSON的脚本为:

I've managed to write a script that can upload the above JSON into my table but I would like to further automate it within a stored procedure. The script that uploads the above JSON is:

DECLARE @jsonVariable varchar(max);

Set @jsonVariable = N'[{"ID":5,"LastName":"Donalds","FirstName":"Dave","Age":23,"City":"London"}]';

INSERT INTO Persons Select * 
FROM OPENJSON (@jsonVariable, N'$')
  WITH (
    ID INT N'$.ID',
    LastName VARCHAR(50) N'$.LastName',
    FirstName VARCHAR(50) N'$.FirstName',
    Age INT N'$.Age',
    City VARCHAR(50) N'$.City'
  ) 

所以我的目标是创建一个具有exec函数的存储过程，

So my objective is to create a stored procedure which has the exec function along the lines of:

 EXEC InsertPersonsJSON 
'[{"ID":5,"LastName":"Donalds","FirstName":"Dave","Age":23,"City":"London"}]'

但是，我不确定如何在创建JSON数据过程的步骤中分离变量.

However, I'm unsure how to separate the variables within the steps of creating a procedure for JSON data.

我想要的结果是表中的最后一行:

My desired result is the last row in my table:

ID  LastName    FirstName   Age   City

5   Donalds     Dave        23    London

感谢您的帮助，如果我的问题需要进一步说明，请告诉我！:)

Thank you for your help and please let me know if my question requires further clarification! :)

推荐答案

如果我对您的理解正确，并且想 ...在SQL Server中创建一个存储过程，该存储过程可以接受JSON数据并将其上传到将指定的表格放到其相关列中，您真的很亲密.您需要创建此存储过程并包括您的SQL语句:

If I understand you correctly and you want to ... create a stored procedure, in SQL Server, that can accept JSON data and upload it into a specified table into its relevant columns, you are really close. You need to create this stored procedure and include your SQL statement:

存储过程:

CREATE PROCEDURE InsertPersonsJSON (
    @JsonData NVARCHAR(MAX)
)
AS
BEGIN
    DECLARE @err int

    INSERT INTO Persons (ID, LastName, FirstName, Age, City)
    SELECT ID, LastName, FirstName, Age, City
    FROM OPENJSON (@JsonData, N'$') WITH (
        ID INT N'$.ID',
        LastName VARCHAR(50) N'$.LastName',
        FirstName VARCHAR(50) N'$.FirstName',
        Age INT N'$.Age',
        City VARCHAR(50) N'$.City'
    )   

    SELECT @err = @@ERROR
    RETURN (@err)
END

执行:

DECLARE @RC int
DECLARE @JsonData nvarchar(max)

SET @JsonData = N'[{"ID":5,"LastName":"Donalds","FirstName":"Dave","Age":23,"City":"London"}]'
EXECUTE @RC = InsertPersonsJSON @JsonData

IF @RC = 0 PRINT 'OK'
ELSE PRINT 'Error'

这篇关于创建一个存储过程，该过程在读取JSON字符串后会提取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！