T-SQL to support yorek/azure-functions-save-form-data-to-sql-azure.csx
/* | |
Expected JSON: | |
{ | |
"Subject": "Form's Subject", | |
"FirstName": "Davide", | |
"LastName": "Mauri", | |
"EmailAddress": "info@davidemauri.it", | |
"OtherField1": "OtherValue1", | |
"OtherField2": "OtherValue2", | |
[...] | |
} | |
*/ | |
create table [dbo].[Contact.Forms] | |
( | |
[FormId] [int] identity(1,1) not null, | |
[FirstName] [nvarchar](50) null, | |
[LastName] [nvarchar](50) null, | |
[EmailAddress] [nvarchar](100) not null, | |
[SignupUTC] [datetime2](0) not null default (sysutcdatetime()), | |
[FormData] [nvarchar](max) null constraint [ck__formdata] check ((isjson([FormData])=(1))), | |
[Subject] [nvarchar](100) null, | |
constraint [pk__contact_forms] primary key clustered | |
( | |
[FormId] asc | |
) | |
) | |
go | |
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 | |
; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment