sql - 存储过程(删除发票)

Question

我正在尝试找出一种方法来创建用于从数据库中删除发票的存储过程。

三个表受到影响:

• INVOICE - 包含 InvoiceID、Date 和 CustomerID (fk)
• INVOICELINE - 包含 InvoicelineID、InvoiceID (fk)、ArticleID (fk)、数量、价格和折扣

• ARTICLE - 包含带有文章名称、数量、价格和折扣的文章。

  这就是我想要的:

  1. 您用一个参数 InvoiceID 调用 SP
  2. 发票行上的项目编号将添加回 Article 表中该项目的数量。
  3. 删除发票上的所有发票行
  4. 发票已删除

以下是我目前所拥有的，这是一个用于删除发票行的 SP。我希望从名为 DeleteInvoice 的 SP 调用此 SP。问题是我无法弄清楚应该如何设计 SP 才能完成这项工作，例如，我如何遍历发票上的发票行？如果有人能在这里提供帮助，我们会很高兴。

删除发票行的SP(应该从正在删除发票的SP调用，有发票行的次数):

@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

Answer 1

我假设您在 @InvoiceID 声明之前故意砍掉了 CREATE/ALTER PROCEDURE 行。您可以在事务中使用三个语句来执行此操作 - 无需多个过程或循环:

@InvoiceID int
AS
BEGIN
  BEGIN TRY
    BEGIN TRANSACTION;

    -- This reclaims inventory to the Article table
    WITH R AS (
      SELECT ArticleID, SUM(Quantity) ReclaimedQuantity
      FROM InvoiceLine
      WHERE InvoiceID = @InvoiceID
      GROUP BY ArticleID
    )
    UPDATE Article
    SET Quantity = Quantity + R.ReclaimedQuantity
    FROM Article INNER JOIN
         R ON Article.ArticleID = R.ArticleID;

    -- Removes invoice line items
    DELETE FROM InvoiceLine
    WHERE InvoiceID = @InvoiceID;

    -- Removes the invoice header item
    DELETE FROM Invoice
    WHERE InvoiceID = @InvoiceID;

    COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION;
  END CATCH
END