SQL Stored Procedure - JSON to SQL

  • Posted:
  • Proposals: 1
  • Remote
  • #1527156
  • Expired
Konstantin S.
has already sent a proposal.
  • 0

Description

Experience Level: Entry
General information for the website: SQL Stored Procedure - JSON to SQL
Description of requirements/features: SQL Stored Procedure - JSON to SQL
Extra notes: --> I would like to amend the following code:

create procedure [dbo].[InsertContactForm]
@payload nvarchar(max)
as
if (isjson(@payload) != 1) throw 50001, 'JSON expected', 1;
with cte as
(
select
*,
@payload as FormData
from
openjson(@payload) with
(
[Subject] nvarchar(100),
FirstName nvarchar(50),
LastName nvarchar(50),
EmailAddress nvarchar(100)
)
)
insert into [dbo].[Contact.Forms]
([Subject], FirstName, LastName, EmailAddress, FormData)
select
*
from
cte
;

--> To work with a different table:

CREATE TABLE [dbo].[Incoming]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[IP] [varchar](50) NULL,
[Time] [datetime] NULL
)
ALTER TABLE [dbo].[Incoming] ADD CONSTRAINT PK__Incoming__3214EC27F4AC4D56 PRIMARY KEY ([ID])

--> And only import IP and Time from the JSON payload which looks like this:

{
"transaction": {
"IP": "7797",
"Time": "2017-01-01"
}
}

New Proposal

Create an account now and send a proposal now to get this job.

Sign up

Clarification Board Ask a Question

    There are no clarification messages.