如何让存储过程在调用另一个存储过程之前操纵一组记录？

我想让一个存储过程调用另一个存储过程。但是，第二个过程必须在变量提供的数据集中的每个记录都运行了第一个过程之后才能运行。第一过程可以在1-无限数量的记录上运行，具体取决于用户输入。一旦处理完所有记录，我希望它调用第二个过程。

我怎么做？

当我现在尝试调用第二个过程时，第一个过程的每条记录都会调用它。但是，在第二个过程开始之前，我需要第一个过程在所有记录上运行。

我尝试过两种方法。

只需Exec sp2在第一个过程结束时添加

创建另一个过程以依次调用两个过程

Exec sp1
       @variable1 = ...
       @ariable2 = ....

  Exec sp2 (no variable passing required)

如果我先执行sp1然后sp2手动执行，则这两个过程将完美工作。

第一步（为了清楚起见，省略了一些更新步骤）

ALTER proc [dbo].[GenerateARInvoiceOriginalBU]
  @workorderid int
 ,@clientpo varchar(50) 
 ,@invoicenumber varchar(15)
 ,@invoicedate date
 ,@accountingdate date
as begin

-- insert invoice ----------
insert invoices(customer,job,invoicedate,accountingdate,invoicenumber,isquote,quoteexpiry,description,pretax,tax,posted)
select j.jarcust,j.job,@invoicedate,@accountingdate,@invoicenumber,0,null,'',0,0,0 
from workorders w
join jobs j on w.job=j.job
where w.workorderid=@workorderid

-- insert invoice items ----------   
insert invoiceitems
(invoiceid,workorderid,workorderdescription,extra,extradescription,itemid,itemdescription,clientpo
,qty,price,uom,pretax,tax,taxgroup,taxrate,costcode,revenueaccount,holdbackpcnt
,billedqty,billedpretax,billedpercent,contractqty,contractprice,contractpretax)
select ident_current('invoices') invoiceid,e.workorderid,w.description workorderdesc,e.extra,e.description extradesc,0 itemid,e.description itemdesc,e.clientpo
      ,e.qty
      ,e.price,'' uom
      ,e.pretax 
      ,e.tax
      ,e.taxgroup,e.taxrate,e.costcode,e.revenueaccount,e.holdbackpcnt
      ,e.qtybilled,e.pretaxbilled,0 billedpercent,e.qty,e.price,e.pretax
  from workorders w
      inner join workorderextras e on (w.workorderid=e.workorderid)
where ISNULL(e.costcode,'')<>''
  and ISNULL(e.pretax,0)<>0
  and ISNULL(e.pretaxbilled,0)=0
  and w.workorderid=@workorderid
  and ISNULL(e.clientpo,'')=ISNULL(@ClientPO,'')
union all
select ident_current('invoices'),i.workorderid,max(w.description) workorderdesc,i.extra,max(e.description) extradesc,i.itemid,i.description itemdesc,i.clientpo
      ,e.qty*i.qty qty
      ,i.price,i.uom
      ,i.pretax
      ,i.tax
      ,i.taxgroup,i.taxrate,i.costcode,i.revenueaccount,i.holdbackpcnt
      ,i.qtybilled,i.pretaxbilled,0 billedpercent,e.qty*i.qty,i.price,i.pretax
  from workorders w
      inner join workorderextras e on (w.workorderid=e.workorderid)
      inner join workorderitems i on (e.workorderid=i.workorderid and e.extra=i.extra)
      left outer join completedtasks ct on i.workorderid=ct.workorderid and i.taskcode=ct.taskcode
where (ISNULL(i.taskcode,'')='' or ISNULL(ct.taskcode,'')<>'')
  and ISNULL(e.costcode,'')=''
  and ISNULL(i.pretax,0)<>0
  and ISNULL(i.pretaxbilled,0)=0
  and w.workorderid=@workorderid
  and ISNULL(i.clientpo,'')=ISNULL(@ClientPO,'')
group by w.arcustomer,i.workorderid,i.extra,i.itemid,i.description,i.clientpo,e.qty*i.qty,i.qtybilled,i.pretax,i.tax,i.pretaxbilled,i.taxgroup,i.taxrate,i.costcode,i.revenueaccount,i.holdbackpcnt,i.price,i.uom
order by 1,2,5

-- set invoice amount ---------- 
update invoices 
set pretax= (select sum(pretax) from invoiceitems where invoiceid=ident_current('invoices'))
   ,tax   = (select sum(tax) from invoiceitems where invoiceid=ident_current('invoices'))
from invoices
where invoiceid=ident_current('invoices')

第二程序

ALTER proc [dbo].[Generate_AR_Invoice_Combine]
as 
begin

update invoices set pretax=a.Pretax, tax=a.tax, oldid=1 from
CombineInvoices a where invoices.invoiceid=a.invoiceid
;
update invoiceitems
set invoiceid = cimi.mininvoiceid
from invoiceitems ii 
join combineinvoiceitems cii on ii.InvoiceItemID = cii.invoiceitemid
join dbo.combine_inv_items_min_invoice cimi on cii.job = cimi.job
--;
update invoices 
set combine=1 where isnull(combine,0) = '0' and oldid = '1'
;
delete from invoices where isnull(combine,0)=0 and isnull(oldid,0)=0
end

因此，第一个过程根据用户选择插入多个发票记录和发票项目记录。第二个过程将多个发票项目合并到每个作业的一张发票上。我将更改第一个过程以自动执行此操作，但该过程将在其他不想合并发票的地方使用。

如果我分别运行它们，则第一个过程将插入所有记录，然后第二个过程将立即对所有新记录进行操作。如果我以上面显示的两种方式调用它们，则第二个过程将对每个记录进行单独操作，而不是一次执行。

这是第二个过程中使用的视图。

ALTER VIEW [dbo].[CombineInvoices]
AS
SELECT        MIN(i.InvoiceID) AS InvoiceID, MIN(i.InvoiceNumber) AS InvoiceNumber, i.Job, dbo.Combine_Inv_Items_Sums.Pretax, dbo.Combine_Inv_Items_Sums.Tax
FROM            dbo.Invoices AS i INNER JOIN
                         dbo.Combine_Inv_Items_Sums ON i.Job = dbo.Combine_Inv_Items_Sums.Job
WHERE        (ISNULL(i.Combine, 0) = 0)
GROUP BY i.Job, dbo.Combine_Inv_Items_Sums.Pretax, dbo.Combine_Inv_Items_Sums.Tax

ALTER VIEW [dbo].[CombineInvoiceItems]
AS
SELECT        i.Job, ii.InvoiceItemID, i.InvoiceNumber, i.InvoiceID
FROM            dbo.Invoices AS i INNER JOIN
                         dbo.InvoiceItems AS ii ON i.InvoiceID = ii.InvoiceID
WHERE        (ISNULL(i.Combine, '') = 0)
GROUP BY i.Job, ii.InvoiceItemID, i.InvoiceNumber, i.InvoiceID

ALTER VIEW [dbo].[Combine_Inv_Items_Sums]
AS
SELECT        cii.Job, ROUND(SUM(ii.Pretax), 2) AS Pretax, ROUND(SUM(ii.Tax), 2) AS Tax
FROM            dbo.CombineInvoiceItems AS cii INNER JOIN
                         dbo.InvoiceItems AS ii ON cii.InvoiceItemID = ii.InvoiceItemID
GROUP BY cii.Job

ALTER VIEW [dbo].[Combine_Inv_Items_Min_Invoice]
AS
SELECT        MIN(InvoiceID) AS MinInvoiceid, Job
FROM            dbo.CombineInvoiceItems
GROUP BY Job