Sql-server – SQL Server: Stored Procedure to add parent & child records

sql serverstored-procedures

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?

Best Answer

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:

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:

Related Question