Executing cte expression in a dynamic sql

Question

I have the following cte expression which I am try to run in a dynamic sql but I am getting invalid identifiers error in it:

DECLARE @t TABLE ( ID INT, V float, D DATE )

INSERT  INTO @t
VALUES  ( 1, 1.2, '2014-01-01' ),
        ( 1, 1.33, '2014-01-02' ),
        ( 1, 1.33, '2014-01-03' ),
        ( 1, 7, '2014-01-04' ),
        ( 2, 5, '2014-01-04' ),
        ( 2, 8, '2014-01-10' ),
        ( 2, 11, '2014-01-05' );

DECLARE @DealClauseString nvarchar(max)
SET @DealClauseString =';WITH    filter
          AS ( SELECT   ID ,
                        D ,
                        V ,
                        ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY D DESC ) AS RN
               FROM     @t where id =1
             ),
        cte
          AS ( SELECT   ID ,
                        D ,
                        V ,
                        MIN(D) OVER ( PARTITION BY ID ORDER BY D ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS Min ,
                        MAX(D) OVER ( PARTITION BY ID ORDER BY D ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS Max
               FROM     filter
               WHERE    RN <= 2
             )
    SELECT  c1.ID ,
            c2.V - c1.V AS V
    FROM    cte c1
            JOIN cte c2 ON c1.ID = c2.ID AND c1.D < c2.D
    WHERE   ( c1.D = c1.MIN OR c1.D = c1.MAX ) 
            AND ( c2.D = c2.MIN OR c2.D = c2.MAX ) '

exec @DealClauseString

Any suggestions?

Also I was going to run this cte expression in a while loop. Are there any performance issue running cte expression in a loop?

For what it's worth, I don't think the down votes were warranted as your question has a clear problem with everything contained to allow the problem to be reproduced. The looping part does however go on a bit of a tangent from the original problem, and it's something you should test now that you've got it working and assess for yourself before asking that question. We don't have your source data, so we can't possibly judge how it will perform. — Tanner, Mar 11 '15 at 15:53

score 2 · Accepted Answer · edited May 23 '17 at 12:22

Firstly, you can't use a table variable with dynamic sql, so you should use a #temp table instead, although this might just be relevant to your example code.

Secondly, if you are using dynamic sql, you either need to place the variable @DealClauseString in brackets or use sp_executesql, otherwise SQL Server assumes that you're calling a stored procedure.

CREATE TABLE #t ( ID INT, V FLOAT, D DATE )

INSERT  INTO #t
VALUES  ( 1, 1.2, '2014-01-01' ),
        ( 1, 1.33, '2014-01-02' ),
        ( 1, 1.33, '2014-01-03' ),
        ( 1, 7, '2014-01-04' ),
        ( 2, 5, '2014-01-04' ),
        ( 2, 8, '2014-01-10' ),
        ( 2, 11, '2014-01-05' );

DECLARE @DealClauseString NVARCHAR(MAX)
SET @DealClauseString = ';WITH    filter
          AS ( SELECT   ID ,
                        D ,
                        V ,
                        ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY D DESC ) AS RN
               FROM     #t where id =1
             ),
        cte
          AS ( SELECT   ID ,
                        D ,
                        V ,
                        MIN(D) OVER ( PARTITION BY ID ORDER BY D ROWS 
                                      BETWEEN UNBOUNDED PRECEDING 
                                      AND UNBOUNDED FOLLOWING ) AS Min ,
                        MAX(D) OVER ( PARTITION BY ID ORDER BY D ROWS 
                                      BETWEEN UNBOUNDED PRECEDING AND 
                                      UNBOUNDED FOLLOWING ) AS Max
               FROM     filter
               WHERE    RN <= 2
             )
    SELECT  c1.ID ,
            c2.V - c1.V AS V
    FROM    cte c1
            JOIN cte c2 ON c1.ID = c2.ID AND c1.D < c2.D
    WHERE   ( c1.D = c1.MIN OR c1.D = c1.MAX ) 
            AND ( c2.D = c2.MIN OR c2.D = c2.MAX ) '

EXEC (@DealClauseString)

Results:

ID  V
1   5.67

Executing cte expression in a dynamic sql

1 Answers1