Twice today, I've had basically the same question in the newsgroups. It boils down to this: the poster wants to send an array of objects (an object graph) to a stored procedure and have that procedure inserts into n-many tables. In other words, they don't want to call n-many procs from the client side. One send, one result.
However, the problem is that T-SQL doesn't have the idea of an array, so you can't pass an array of objects into a stored procedure. And, no, it doesn't make a difference if that procedure is a T-SQL or a CLR procedure since they both use the T-SQL type system at the interface. But while you can't send an array of objects, you can send a single string. What does that by you? Well, it also means you can send an XML document-like instance to procedure (think "oh, I'll just serialize the object graph to XML and send that.")
But how? Here's an example:
use master
go
drop database procexample
go
create database procexample
go
use procexample
go
create table dbo.tableA
(pkid tinyint identity(1,1) primary key
,descr varchar(50) not null)
go
create table dbo.tableB
(
pkid tinyint identity(1,1) primary key
,pkid_tableA tinyint not null
constraint fk_tableB_tableA_PKID
foreign key references dbo.tableA(pkid)
,descr varchar(50) not null
)
go
create procedure dbo.InsertFromXML
(@doc xml)
as begin
-- Use this to capture the inserted identity, etc.
declare @insertedIntoA table (pkid tinyint,descr varchar(50))
set nocount on
begin try
begin tran
-- use a CTE to feed values into the following insert
;with a(descr) as
-- Get the text value of the A node
(select @doc.value('(/tableA/text())[1]','varchar(50)'))
insert into dbo.tableA(descr)
-- capture the inserted IDs
output inserted.* into @insertedIntoA
-- value from the CTE
select descr from a
-- Now for the sub-parts...
-- use a CTE to get the tableB values
;with b(id,descr) as
-- Get the PKID from table and each of the values from the
-- //tableB/text() nodes (which is a table valued function)
-- and use cross apply to create the cartessian product.
(select A.pkid,b.item.value('.','varchar(50)')
from @insertedIntoA a
cross apply @doc.nodes('/tableA/tableB') as b(item))
-- Dump the CTE into the target table
insert into tableB(pkid_tableA,descr)
select * from b
-- Done, save.
commit tran
end try
begin catch
-- Something bad happened? Kill the transaction and rollback.
rollback tran
end catch
end
-- Show that it works
declare @doc xml
set @doc = '<tableA>Beer<tableB>Water</tableB><tableB>Malt</tableB><tableB>Hops</tableB><tableB>Yeast</tableB></tableA>'
exec dbo.InsertFromXML @doc
go
select a.descr,b.descr
from dbo.tableA a
join dbo.tableB b
on a.pkid = b.pkid_tableA
go
Of course, you could do this with SQLCLR, but that's another episode...
[0] Sorry, Don, since I've not gotten any Microbes (not even Saccharomyces cerevisiae) from you, I had to do it.