多次创建cte并删除cte

Question

我在存储过程中使用cte。

我必须多次使用它(为不同的ids填充cte )

如何删除或删除当前的cte

谢谢

;WITH PAYOUT_CTE(REGNKEY, REGNTPE) AS (
    SELECT  REG_KEY, 'Parent'
    FROM    ML_MSTR_REGN A (NOLOCK)
    WHERE   A.COMP_NO = @COMP_NO
        AND     A.REG_KEY = @CUR_KEY

    UNION   ALL

    SELECT  B.REG_KEY, 'Child0'
    FROM    PAYOUT_CTE
    INNER JOIN ML_MSTR_REGN B
        ON  B.COMP_NO = @COMP_NO
           AND  B.ORG_KEY = PAYOUT_CTE.REGNKEY
            )
SELECT * INTO #PAYOUT_CTE_TMP FROM PAYOUT_CTE

我解决了这个问题，因为cte是在insert语句之后自动删除的，只有temp表#PAYOUT_CTE_TMP导致了问题。

Answer 1

我看到这种工作的唯一方法是将CTE代码放在内联表值函数中并调用它。

您的代码应该类似于：

CREATE FUNCTION TVFN_PAYOUT_CTE (@COMP_NO INT, @CUR_KEY INT) 
RETURNS TABLE
AS
RETURN (

WITH PAYOUT_CTE(REGNKEY, REGNTPE) AS (
    SELECT  REG_KEY, 'Parent'
    FROM    ML_MSTR_REGN A (NOLOCK)
    WHERE   A.COMP_NO = @COMP_NO
        AND     A.REG_KEY = @CUR_KEY

    UNION   ALL

    SELECT  B.REG_KEY, 'Child0'
    FROM    PAYOUT_CTE
    INNER JOIN ML_MSTR_REGN B
        ON  B.COMP_NO = @COMP_NO
           AND  B.ORG_KEY = PAYOUT_CTE.REGNKEY
            )
SELECT * 
FROM PAYOUT_CTE
)

然后，我只需交叉应用或外部应用到这个表值函数中，就可以根据输入值生成数据。

如果COMP_NO和CUR_KEY是"TEST"表中的值，我可以使用带有COMP_NO和CUR_KEY的每一行来运行这些值并保存它们，如下所示：

SELECT PC.*
    INTO #PAYOUT_CTE_TMP
FROM TEST T
    CROSS APPLY TVFN_PAYOUT_CTE (T.COMP_NO, T.CUR_KEY) PC

通过这样做，就可以重建TEST表中的每一行并运行CTE。