2

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?
  • 1
    You can pass parameters to stored procs. And stored procs can select data. You can also break this into seperate sp's and call them as required. So 1st sp could insert header row and return inv id. Call 2nd sp with inv id as parameter.. it does insert into table as select from... etc. – Sir Swears-a-lot Feb 11 '17 at 9:22
  • 1
    Where does your nvoice data cone from? Are the line items already in a table? Sale items? Inventory? Products? Account balance? – Sir Swears-a-lot Feb 11 '17 at 9:26
  • What software/code is going to create the invoice? Do you just want the sp to store the data? Or do you want it to calculate the invoice? – Sir Swears-a-lot Feb 11 '17 at 9:32
  • @Peter the plan was to call the procedure from an SQL command line. Something like exec addInvoice(…); or whatever is the correct syntax for calling a procedure. – Manngo Feb 11 '17 at 9:53
  • 1
    As @Peter indicated, we need to know where your source data is coming from - even if you're not familiar with TSQL stored procedure specifics, you could post some pseudo-code of what you're trying to achieve. Specifically, what parameters to you intend to pass into the stored procedure. Your answers will help us offer suggestions. – Scott Hodgin Feb 11 '17 at 12:11
3

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:

  • Thanks for your detailed answer. I’m trying it out and will get back to you. – Manngo Feb 11 '17 at 23:52
  • Thanks again for your answer. I’ve added my own simplified answer below, but yours is a much fuller one. I didn’t know about the sequence alternative to identity — it’s more in line with other databases I use. – Manngo Feb 12 '17 at 1:28
1

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.