合并多个结果

Question

我有多个条件明智的存储过程，我想结果集合并成单一的程序......

ALTER PROCEDURE [dbo].[usp_TotalReceivedUnits_Country] 
@ContractCode NVARCHAR (20), 
@CountryCode NVARCHAR (10), 
@Years NVARCHAR(1000) 
AS 
BEGIN 
     SELECT ISNULL(SUM(S.NettoWeight), 0) TotalNettoWeight, COUNT(S.ShipmentID) Loads, ReceivalDateYear = YEAR(S.ReceivalDate) 
     FROM Shipment S 
     WHERE S.ContractCode = @ContractCode AND 
       S.[OrigCountryCode] = @CountryCode AND 
       S.ClosedYN = 0 AND 
       S.Shipped = 1 AND 
       S.DeletedYN = 0 AND 
       S.OutInbound = 2 AND 
       YEAR(S.ReceivalDate) IN (SELECT Data FROM dbo.fnSplitString(@Years, ',')) 
     GROUP BY YEAR(S.ReceivalDate) 
END 
GO 

ALTER PROCEDURE [dbo].[usp_TotalProcessedUnits_Country] 
@ContractCode NVARCHAR (20), 
@CountryCode NVARCHAR (10), 
@Years NVARCHAR(1000) 
AS 
BEGIN 
    SELECT ISNULL(SUM(S.NettoWeight), 0) TotalNettoWeight, COUNT(S.ShipmentID) Loads, ReceivalDateYear = YEAR(S.ReceivalDate) 
    FROM Shipment S with (nolock) 
    WHERE S.ContractCode = @ContractCode AND 
      S.OrigCountryCode = @CountryCode AND 
      S.ClosedYN = 1 AND 
      S.Shipped = 1 AND 
      S.FinanceDetailsYN = 0 AND 
      S.DeletedYN = 0 AND 
      S.OutInbound = 2 AND 
      YEAR(S.ReceivalDate) IN (SELECT Data FROM dbo.fnSplitString(@Years, ',')) 
    GROUP BY YEAR(S.ReceivalDate) 
END 
GO 

以上两种方法都用同样的处理结果数量和接收到的总结，但我怎么能结合他们到一个具有多个条件的查询？

Answer 1

如果我正确理解你的问题，你应该能够输出加载到临时表，然后选择：

CREATE TABLE #out1(TotalNettoWeight INT, Loads INT, ReceivalDateYear DATETIME2); 
CREATE TABLE #out2(TotalNettoWeight INT, Loads INT, ReceivalDateYear DATETIME2); 
GO 

INSERT #out1 
EXEC [dbo].[usp_TotalReceivedUnits_Country] 123,6,10; 

INSERT #out2 
EXEC [dbo].[usp_TotalProcessedUnits_Country] 123,6,10; 
GO 

SELECT * FROM #out1 
UNION 
SELECT * FROM #out2 
GO 

DROP TABLE #out1; 
DROP TABLE #out2; 
GO