Передача списка настраиваемого объекта в хранимую процедуру SQL Server

Я хочу передать список пользовательских объектов только с одним вызовом хранимой процедуры. Вот объект

public class OGFormResponse { public string Response {get; set;} public OGFormLabelVO FormLabel {get; set;} } public class OGFormLabelVO { public int OGFormLabelKey {get; set;} public string FormType {get; set;} public string LabelText {get; set;} public string ControlName {get; set;} public string DisplayStatus {get; set;} public string LabelType = {get; set;} public bool IsActive {get; set;} public string LabelParentControlName {get; set;} } 

Вот связь с базой данных

 CREATE TABLE [dbo].[OGFormLabels]( [OGFormLabelKey] [int] IDENTITY(1,1) NOT NULL, [OGFLText] [nvarchar](max) NULL, [OGFLControlName] [nvarchar](50) NOT NULL, [OGFLIsActive] [bit] NOT NULL, [OGFLDisplayStatusKey] [int] NOT NULL, [OGFLFormTypeKey] [int] NOT NULL, [OGFLLabelTypeKey] [int] NOT NULL, [OGFLParentKey] [int] NULL, [OGFLBeginDate] [datetime] NOT NULL, [OGFLBeginUser] [varchar](40) NOT NULL, [OGFLUpdateDate] [datetime] NOT NULL, [OGFLUpdateUser] [varchar](40) NOT NULL, CONSTRAINT [PK_OGFormLabel] PRIMARY KEY CLUSTERED ( [OGFormLabelKey] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[OGFormLabels] ADD CONSTRAINT [DF_OGFormLabel_OGFLBeginDate] DEFAULT (getdate()) FOR [OGFLBeginDate] GO ALTER TABLE [dbo].[OGFormLabels] ADD CONSTRAINT [DF_OGFormLabel_OGFLBeginUser] DEFAULT ('dbo') FOR [OGFLBeginUser] GO ALTER TABLE [dbo].[OGFormLabels] ADD CONSTRAINT [DF_OGFormLabel_OGFLUpdateDate] DEFAULT (getdate()) FOR [OGFLUpdateDate] GO ALTER TABLE [dbo].[OGFormLabels] ADD CONSTRAINT [DF_OGFormLabel_OGFLUpdateUser] DEFAULT ('dbo') FOR [OGFLUpdateUser] GO ALTER TABLE [dbo].[OGFormLabels] WITH CHECK ADD CONSTRAINT [FK_OGFormLabel_OGFormStatus] FOREIGN KEY([OGFLFormTypeKey]) REFERENCES [dbo].[OGDisplayStatus] ([OGDisplayStatusKey]) GO ALTER TABLE [dbo].[OGFormLabels] CHECK CONSTRAINT [FK_OGFormLabel_OGFormStatus] GO ALTER TABLE [dbo].[OGFormLabels] WITH CHECK ADD CONSTRAINT [FK_OGFormLabel_OGFormType] FOREIGN KEY([OGFLFormTypeKey]) REFERENCES [dbo].[OGFormType] ([OGFormTypeKey]) GO ALTER TABLE [dbo].[OGFormLabels] CHECK CONSTRAINT [FK_OGFormLabel_OGFormType] GO ALTER TABLE [dbo].[OGFormLabels] WITH CHECK ADD CONSTRAINT [FK_OGFormLabel_OGLabelType] FOREIGN KEY([OGFLLabelTypeKey]) REFERENCES [dbo].[OGLabelType] ([OGLabelTypeKey]) GO ALTER TABLE [dbo].[OGFormLabels] CHECK CONSTRAINT [FK_OGFormLabel_OGLabelType] GO CREATE TABLE [dbo].[OGFormResponses]( [OGFormResponseKey] [int] IDENTITY(1,1) NOT NULL, [OGRFormKey] [int] NOT NULL, [OGRFormLabelKey] [int] NOT NULL, [OGRResponse] [nvarchar](max) NOT NULL, [OGRBeginDate] [datetime] NOT NULL, [OGRBeginUser] [varchar](40) NOT NULL, [OGRUpdateDate] [datetime] NOT NULL, [OGRUpdateUser] [varchar](40) NOT NULL, CONSTRAINT [PK_OGFormResponse] PRIMARY KEY CLUSTERED ( [OGFormResponseKey] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[OGFormResponses] ADD CONSTRAINT [DF_OGFormResponse_OGRBeginDate] DEFAULT (getdate()) FOR [OGRBeginDate] GO ALTER TABLE [dbo].[OGFormResponses] ADD CONSTRAINT [DF_OGFormResponse_OGRBeginUser] DEFAULT ('dbo') FOR [OGRBeginUser] GO ALTER TABLE [dbo].[OGFormResponses] ADD CONSTRAINT [DF_OGFormResponse_OGRUpdateDate] DEFAULT (getdate()) FOR [OGRUpdateDate] GO ALTER TABLE [dbo].[OGFormResponses] ADD CONSTRAINT [DF_OGFormResponse_OGRUpdateUser] DEFAULT ('dbo') FOR [OGRUpdateUser] GO ALTER TABLE [dbo].[OGFormResponses] WITH CHECK ADD CONSTRAINT [FK_OGFormResponse_OGForm] FOREIGN KEY([OGRFormKey]) REFERENCES [dbo].[OGForm] ([OGFormKey]) GO ALTER TABLE [dbo].[OGFormResponses] CHECK CONSTRAINT [FK_OGFormResponse_OGForm] GO ALTER TABLE [dbo].[OGFormResponses] WITH CHECK ADD CONSTRAINT [FK_OGFormResponse_OGFormLabel] FOREIGN KEY([OGRFormLabelKey]) REFERENCES [dbo].[OGFormLabels] ([OGFormLabelKey]) GO ALTER TABLE [dbo].[OGFormResponses] CHECK CONSTRAINT [FK_OGFormResponse_OGFormLabel] GO 

Таким образом, OGFormResponseVO имеет отношения 1 к 1 с OGFormLabelVO. Я хочу, чтобы вставить список OGFormResponseVO в базу данных через вызов Хранимой процедуры. Я просмотрел параметры таблицы и вы не можете иметь Колонку Тип – другой тип. Есть ли обходной путь для этого, или мне лучше просто передать все свойства дочернего объекта в виде отдельных параметров, или есть лучший способ. Я должен использовать SP, поскольку он является частью более крупного проекта, поэтому другие параметры модели данных недоступны.

• Каковы недостатки табличных значений в хранимом Proc?
• CLR Табличнозначная функция с аргументом массива
• Цикл через параметр таблицы таблицы SQL
• TVP не соответствует типу таблицы
• Предоставляет ли Where clauses с подзапросами в t-sql данные для каждой строки?

 CREATE TYPE [dbo].[OGFormResponse] AS TABLE( [Response] VARCHAR(256), [SequenceId] INT --This is just a temporary sequence (1..N) you can use to map to form labels (see below) ) 

 CREATE PROCEDURE [dbo].[SaveFormStuff] @FormResponses AS [dbo].[OGFormResponse] READONLY, @FormLabels AS [dbo].[OGFormLabelVO] READONLY AS SET NOCOUNT ON; //This stores the PKs of the inserted form labels DECLARE @InsertedFormLabels AS TABLE ( Id INT NOT NULL, SequenceId INT NOT NULL ) INSERT INTO [dbo].[OGFormLabels] (...) SELECT (...) FROM @FormLabels FL OUTPUT inserted.OGFormLabelKey, FL.SequenceId INTO @InsertedFormLabels -- Now you have the newly inserted form label ID mapped to sequence IDs -- Time to insert responses INSERT INTO [dbo].[OGFormResponses] (...) SELECT (...), OGRFormLabelKey = IFL.Id FROM @FormResponses FR INNER JOIN @InsertedFormLabels IFL ON IFL.SequenceId = FR.SequenceId END