Create a gist now

Instantly share code, notes, and snippets.

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