Create Cte Multiple Times And Drop Cte

I am using a cte in stored procedure. I have to use it multiple times ie populate cte for different ids how can I drop or remove current cte thanks ;WITH PAYOUT_CTE(REGNKEY, REGNTP

Solution 1:

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

Your code would look something like:

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.

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

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