SQL Server: Stored Procedure to add parent & child records

Question

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:

1. Create the invoice record
2. Get the primary key of the invoice record (presuming it’s auto-numbered)
3. Loop: add multiple invoiceitem records, with the foreign key set to the primary key in step 2 above.
4. (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?

Answer 1

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

Answer 2

I have accepted @SqlZim’s answer, but felt that the following simplified example, based on his or her answer, might be helpful.

First some sample tables.

create table invoices(
    id int identity(1,1) primary key nonclustered,
    customerid int, --  references customers(id)
    date date,
    total decimal(7,3)
);
create table invoiceitems(
    id int identity(1,1) primary key nonclustered,
    invoiceid int references invoices(id),
    productid int,  --  references products(id)
    quantity int,
    price decimal(5,3)
);

create type invoice as table(
    productid int,
    quantity int,
    price decimal(5,3)
);

In principal, the tables contain some foreign keys which are commented out for simplicity. However, there is one important foreign key: invoiceid int references invoices(id).

A procedure would work as follows:

create procedure addInvoice (
    @customerid int,
    @items invoice readonly,
    @id int=null output
) as
begin
    declare @total decimal(7,3);
    begin transaction;

        insert into invoices(customerid) values(@customerid);
        set @id=scope_identity();

        insert into invoiceitems(invoiceid,productid,quantity,price)
        select @id,productid,quantity,price from @items;

        set @total=(select sum(quantity*price) from @items);
        update invoices set total=@total where id=@id;

    commit transaction;
end;

In this case there is also a field in the parent table which is updated after the child records are added.

There is an optional return parameter, just in case you want to use the newly created primary key of the parent.

To execute the procedure:

declare @items invoice;
INSERT INTO @items(productid,quantity,price)
values
    (13,3,16),
    (14,1,12),
    (19,4,8),
    (11,2,17);
execute addInvoice 42,@items;

This example ignores the return value. If you need it:

declare @id int;
execute addInvoice 42,@items,@id=@id output;
select @id;

@SqlZim’s answer is, of course, more comprehensive. Thanks.

Answer 3

SQL Server stored procedures can accecpt and parse structured types (XML and JSON). While shredding can be quite efficient it is an overhead. Given the choice I would recommend a table valued parameter, as @SqlZim described.

The INSERT statement has an OUTPUT clause. Newly-created identity values can be obtained through it. It is an alternative to SCOPE_IDENTITY(). The advantage is that you receive a table rather than a single value. It does require more coding, however. If, say, invoiceitems also had an identity it would be useful there.

"How do I loop through the data?" You don't. Relational databases should work on sets of rows, not one row at a time. Although language constructs exists that allow this (CURSOR, WHILE), it is almost always a mistake.