SQL Server Iterate through CTE

Question

存储过程 #1：@Country参数返回 1+ 行OrganizationID和MeasurableID列

存储过程 #2：@OrganizationID并@MeasurableID参数，创建一个 CTE(它使用 ROW_NUMBER() OVER (PARTITION BY Entities.ID ORDER BY Contracts.UTCmatched desc) 为每个Entity返回单独的行(

我试图将它们组合成一个存储过程，其中通过 SP1 代码的@Country参数返回(例如 5 行(;然后通过 SP2 代码迭代每一行以返回单个表(如果有 20 个实体，则为 100 行(。

我已经尝试了嵌套和递归 CTE 无济于事。我正在考虑循环/光标和/或将最终结果填充到临时表中，但坦率地说，我有点迷茫(超出了我的经验水平(，因此非常感谢有关采取的方法的建议。

提前谢谢。

SP1

        SELECT
            dbo.Measurables.OrganizationID,
            dbo.Measurables.ID AS MeasurableID
            FROM dbo.Measurables INNER JOIN dbo.Organizations ON dbo.Organizations.ID = dbo.Measurables.OrganizationID
            WHERE   dbo.Measurables.EndUTC > SYSUTCDATETIME ( )  
                AND dbo.Measurables.OrganizationID IN
                                        (
                                                SELECT
                                                    dbo.Countries2Organizations.OrganizationID
                                                FROM
                                                    dbo.Countries2Organizations
                                                WHERE
                                                    dbo.Countries2Organizations.Sport IN 
                                                        (
                                                            SELECT
                                                                dbo.Countries2Sports.SportName
                                                            FROM
                                                                dbo.Countries2Sports
                                                            WHERE
                                                                dbo.Countries2Sports.CountryCode = @CountryCode
                                                        )
                                                AND (   dbo.Countries2Organizations.CountryCode = @CountryCode  OR dbo.Countries2Organizations.CountryCode = '')
                                        )
--Result:     OrganizationID     MeasurableID
                    --1                 2017
                    --1                 2018
                    --2                 2021
                    --3                 2023

SP2

;WITH 
        LastScore as ( SELECT Entities.ID, Results.Score,
                                 Results.UTC,   
                                row_number() over (partition by Entities.ID ORDER BY Results.UTC desc) row1
                            FROM dbo.Entities 
                                INNER JOIN dbo.Results ON dbo.Results.EntityID = dbo.Entities.ID
                            WHERE dbo.Results.MeasurableID = @MeasurableID),

        PreviousScore as ( SELECT Entities.ID,  Results.Score,
                                 Results.UTC,   
                                row_number() over (partition by Entities.ID ORDER BY Results.UTC desc) row2
                            FROM dbo.Entities 
                                INNER JOIN dbo.Results ON dbo.Results.EntityID = dbo.Entities.ID
                            WHERE dbo.Results.MeasurableID = @MeasurableID )

    SELECT @OrganizationID AS OrganizationID, @MeasurableID AS MeasurableID, Entities.ID AS EntityID,    
              LastScore.Score AS LastScore, 
                    LastScore.Score - PreviousScore.Score AS Change,    LastScore.UTC 
    FROM Entities 
            LEFT JOIN LastScore on LastScore.ID = Entities.ID AND row1=1
            LEFT JOIN PreviousScore on PreviousScore.ID = Entities.ID AND row2=2
    WHERE dbo.Entities.OrganizationID = @OrganizationID 

--Result:
 --OrganizationID   MeasurableID   EntityID   LastScore   Change   UTC   
      --1              2017           1          15          2    4/6/17
      --1              2017           1          18         -3    4/8/17

Answer 1

我建议使用函数，而不是存储过程它的工作速度更快：

1(没有循环，数据将在一次查询中被引用

2(使用内联函数查询优化器可以优化整个查询

下面是如何使用函数执行此操作的示例：

CREATE FUNCTION dbo.Func1 (
    @Country    INT
)
RETURNS TABLE
RETURN
    SELECT
        dbo.Measurables.OrganizationID,
        dbo.Measurables.ID AS MeasurableID
    FROM dbo.Measurables
...
CREATE FUNCTION dbo.Func2 (
    @OrganizationID  INT,
    @MeasurableID    INT
)
RETURNS TABLE
RETURN
    WITH 
        LastScore as ( SELECT Entities.ID, Results.Score,
                             Results.UTC,   
...

SELECT *
FROM dbo.Func1(@Country)                    F1
CROSS APPLY dbo.Func2(F1.OrganizationID, F1.MeasurableID) F2