多次创建cte并删除cte [英] create cte multiple times and drop cte

问题描述

我在存储过程中使用了cte。

I am using a cte in stored procedure.

我必须多次使用它，即为不同的ID填充cte

I have to use it multiple times ie populate cte for different ids

如何删除或删除当前的CTE

how can I drop or remove current 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，只有临时表#PAYOUT_CTE_TMP引起了问题。

I resolved it, as cte is automatically removed after the insert statement only the temp table #PAYOUT_CTE_TMP was causing the problem.

推荐答案

我唯一能看到此效果的方法是将CTE编码

The only way I can see this working is if you put the CTE code in a inline table valued function and call that.

您的代码应类似于：

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
)

然后我可以对这个表值函数进行交叉应用或外部应用，以根据输入值生成数据。

Then I can just cross apply or outer apply to this table valued function to generate data based on input values.

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

If COMP_NO and CUR_KEY are values in a "TEST" table, I could use each of the rows with COMP_NO and CUR_KEY to run the CTE with those values and save them, like below:

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

这样做，将重新构建CTE，并针对 TEST 表。

By doing this, the CTE is rebuilt and ran for each row from the TEST table.

这篇关于多次创建cte并删除cte的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！