SQL Server: Stored Procedure to add parent & child records My SQL is pretty good, and I have programming experience in a number of languages. However, I have only dabbled in stored procedures for SQL Server.

I would like to write a procedure to add a parent record with child records in a related table. This is the sort of thing you would do to add an invoice in an invoice table and invoice items to an invoiceitems table.

I know the process would be as follows:


Create the invoice record
Get the primary key of the invoice record (presuming it’s auto-numbered)
Loop: add multiple invoiceitem records, with the foreign key set to the primary key in step 2 above.
(Possibly) update the parent invoice parent record with anything derived from the child records (such as a total).


I can handle most of that with what I already know. However:


how can I send the procedure the data to be processed?
inside the procedure how do I loop through the data?
You could use Table-Valued Parameters to pass a set of InvoiceItems to a procedure that will insert a new Invoice and also insert the InvoiceItems to the InvoiceItems table.

Without knowing your table schema, I am just going to throw in a few columns. 

Example user-defined table type for use as a table-valued parameter:

create type dbo.InvoiceItems_udt as table (
    ProductId int
  , Quantity decimal(9,2)
  , LineTotal decimal(19,2)
    );
go


Example procedure using scope_identity() to get the InvoiceId after inserting a row into dbo.Invoice:

create procedure dbo.Invoice_Insert_WithItems (
    @InvoiceId int output
  , @CustomerId int not null
  , @InvoiceItems dbo.InvoiceItems_udt readonly
) as
begin;
  set nocount on, xact_abort on;
  begin tran;
    insert into dbo.Invoice (CustomerId, InvoiceTotal)
      select @CustomerId, InvoiceTotal = sum(LineTotal)
        from @InvoiceItems i;

    select @InvoiceId = convert(int,scope_identity());

    insert into dbo.InvoiceItems (InvoiceId, ProductId, Quantity, LineTotal)
      select @InvoiceId, ProductId, Quantity, LineTotal
        from @InvoiceItems i;
  commit tran;
end;
go


Table-valued parameter reference:


SQL Server 2008 Table-Valued Parameters and C# Custom Iterators: A Match Made In Heaven! - Leonard Lobel
Using Table-Valued Parameters in SQL Server and .NET - Erland Sommarskog
Maximizing Performance with Table-Valued Parameters - Dan Guzman
User-Defined Table Types - msdn
Table-Valued Parameters - msdn


 

In SQL Server 2012+, an alternative to using an auto-numbered identity() for InvoiceId, you could use a sequence.

Example of how to create and use a sequence as the primary key on dbo.Invoice:

create sequence dbo.InvoiceIdSequence as int
  start with 1
  increment by 1;

create table dbo.Invoice (
    InvoiceId  int not null default next value for dbo.InvoiceIdSequence
  , CustomerId int not null 
  , InvoiceTotal decimal(19,2)
  , constraint pk_Invoice primary key clustered (InvoiceId)
  , constraint fk_Invoice_Customer foreign key (CustomerId)
      references dbo.Customer(CustomerId)
  );


In this example, instead of using scope_identity() after inserting a row into dbo.Invoice,
we would use next value for dbo.InvoiceIdSequence before inserting a row into dbo.Invoice.

create procedure dbo.Invoice_Insert_WithItems (
    @InvoiceId int output
  , @CustomerId int not null
  , @InvoiceItems dbo.InvoiceItems_udt readonly
) as
begin;
  set nocount on, xact_abort on;
  begin tran;
    set @InvoiceId = next value for dbo.InvoiceIdSequence;

    insert into dbo.Invoice (CustomerId, InvoiceId, InvoiceTotal)
      select @CustomerId, @InvoiceId, InvoiceTotal = sum(LineTotal)
        from @InvoiceItems i;

    insert into dbo.InvoiceItems (InvoiceId, ProductId, Quantity, LineTotal)
      select @InvoiceId, ProductId, Quantity, LineTotal
        from @InvoiceItems i;
  commit tran;
end;
go


Sequence reference:


Simple Talk - Sql Server sequence basics - Joe Celko
sequence - msdn 
next value for - msdn