title: Stored procedures (delete invoice)
body: <p>I'm trying to figure out a way to create a stored procedure for deleting an invoice from the db.</p>
  
  <p>Three tables is affected:</p>
  
  <ul>
  <li>INVOICE - Contains InvoiceID, Date and CustomerID (fk)</li>
  <li>INVOICELINE - Contains InvoicelineID, InvoiceID (fk), ArticleID (fk), Quantity, Price and Discount</li>
  <li><p>ARTICLE - Contains articles with Articlename, Quantity, Price and Discount.</p>
  
  <p>This is what I want:</p>
  
  <ol>
  <li>You call the SP with one argument, InvoiceID</li>
  <li>The number of an item on an invoice line is added back to the Article table to the quantity of that item.</li>
  <li>All the invoice lines on the invoice is deleted</li>
  <li>The invoice is deleted</li>
  </ol></li>
  </ul>
  
  <p>Below is what I have so far, which is a SP for deleting an invoice line. I want this SP to be called from a SP called DeleteInvoice. The problem is that I can't figure out how that SP should be designed to be able to get this work, for example how do I loop through the invoice lines on an invoice? Would be glad if anyone could help out here.</p>
  
  <p>The SP for deleting an invoice line (should be called from the SP that's deleting an invoice, the number of times as there are invoice lines):</p>
  
  <pre><code>@InvoiceID int
  AS
  BEGIN
      DECLARE @Articleid int, @Quantity int
      SELECT @Articleid = ArticleID, @Quantity=Quantity
      FROM InvoiceLine
      WHERE InvoiceID=@InvoiceID
  
      BEGIN TRY
          BEGIN TRAN
              UPDATE Article SET Quantity=Quantity+@Quantity
              WHERE ArtikelID=@Articleid;
  
              DELETE FROM InvoiceLine
              WHERE InvoiceLineID=@InvoiceLineID;
          COMMIT TRAN
      END TRY
  
      BEGIN CATCH
          ROLLBACK TRAN
          RAISERROR ('Couldn't delete the invoice line!',16,1)
      END CATCH   
  END
  </code></pre>