title: Creating a stored procedure which ingests data after reading a JSON string
body: <p>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:</p>
  
  <pre><code>[{"ID":5,"LastName":"Donalds","FirstName":"Dave","Age":23,"City":"London"}]
  </code></pre>
  
  <p>This above string can be produced with the following query:</p>
  
  <pre><code>SELECT 
  5 as 'ID',
  'Donalds' as 'LastName',
  'Dave' as 'FirstName',
  23 as 'Age',
  'London' as 'City'
  FOR JSON PATH
  </code></pre>
  
  <p>I've managed to write a script which can upload into my table called 'Persons'. My table has the following CREATE script:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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'
    ) 
  </code></pre>
  
  <p>So my objective is to create a stored procedure which has the exec function along the lines of:</p>
  
  <pre><code> EXEC InsertPersonsJSON 
  '[{"ID":5,"LastName":"Donalds","FirstName":"Dave","Age":23,"City":"London"}]'
  </code></pre>
  
  <p>However, I'm unsure how to separate the variables within the steps of creating a procedure for JSON data.</p>
  
  <p>My desired result is the last row in my table:</p>
  
  <pre><code>ID  LastName    FirstName   Age   City
  
  5   Donalds     Dave        23    London
  </code></pre>
  
  <p>Thank you for your help and please let me know if my question requires further clarification! :)</p>