sql server游标临时表

 

ALTER PROCEDURE MKTG_DiffeRenceDailyReport
@CustomerName varchar(100),
@ShipDate datetime
AS
BEGIN
 Set NoCount ON
 
  DECLARE
  @NoReceivedPackages varchar(2000),
  @CurrBillTrackId varchar(30),
  @CurrPackageId varchar(30)
 
 SELECT a.BillTrackId, a.Pieces, (case isnull(a.Canceled, 0) when 1 then '√' end) as Canceled,
 c.FacilityName AS CustomerName, a.ShipDate,
 CAST('' as varchar(2000)) AS NoReceivedPackageIdList,
 COUNT(b.PackageTrackId) AS ReceivedPieces
 INTO #t1
 FROM MKTG_ShipBills a
 LEFT JOIN MKTG_ShipmentsCheckin b ON a.BillTrackId = b.BillTrackId
 LEFT JOIN Common_Facilities c ON a.CustomerId = c.FacilityId
 WHERE c.FacilityName = @CustomerName AND a.ShipDate = @ShipDate
 GROUP BY a.BillTrackId, a.Pieces, a.Canceled, c.FacilityName, a.ShipDate
 ORDER BY a.BillTrackId
 
 DECLARE c1 CURSOR FOR SELECT BillTrackId FROM #t1
 OPEN c1
 FETCH NEXT FROM c1 INTO @CurrBillTrackId
 WHILE @@FETCH_STATUS = 0
 BEGIN
   SELECT a.PackageTrackId
   INTO #t2
   FROM MKTG_ShipPackages a
  left join MKTG_ShipmentsCheckin b ON a.BillTrackId = b.BillTrackId
  left join MKTG_ShipBills d ON a.BillTrackId = d.BillTrackId
  LEFT JOIN Common_Facilities c ON c.FacilityId = d.CustomerId
  WHERE a.PackageTrackId not in (SELECT PackageTrackId FROM MKTG_ShipmentsCheckin)
  and c.FacilityName = @CustomerName AND d.ShipDate = @ShipDate
  and a.BillTrackId = @CurrBillTrackId
  SET @NoReceivedPackages = ''
  DECLARE c2 CURSOR FOR SELECT PackageTrackId FROM #t2
  OPEN c2
  FETCH NEXT FROM c2 INTO @CurrPackageId
  WHILE @@FETCH_STATUS = 0
  BEGIN
   SET @NoReceivedPackages = @NoReceivedPackages + ';' + @CurrPackageId
    FETCH NEXT FROM c2 INTO @CurrPackageId
  END
  SET @NoReceivedPackages = SUBSTRING(@NoReceivedPackages, 2, LEN(@NoReceivedPackages))
  UPDATE #t1 SET NoReceivedPackageIdList = @NoReceivedPackages WHERE BillTrackId = @CurrBillTrackId;
  DROP TABLE #t2
  CLOSE c2
  DEALLOCATE c2 
  FETCH NEXT FROM c1 INTO @CurrBillTrackId
 END
 CLOSE c1
 DEALLOCATE c1
 
 SELECT * FROM #t1
 DROP TABLE #t1
END