T-SQL to support yorek/azure-functions-save-form-data-to-sql-azure.csx

azure-functions-save-form-data-to-sql-azure.sql

/*

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
;