Incorrect syntax after CTE

Question

declare @SQL nvarchar(max);
with tbl1 as
    (
    SELECT ...
    ),
tbl2 as
    (
    SELECT ...
    ),
tbl15 as
    (
    select [tbl1].[DT],
    [tbl1].[Kr_IL.BTS],
    [tbl2].[Kr_IL.CS],
    from [tbl1], [tbl2]
    where 
    [tbl1].[DT] = [tbl2].[DT] 
    and [tbl1].[DT] = [tbl3].[DT] 
    )

set @SQL = 'select [tbl15].[DT], '
if @tag1 = 1 set @SQL = @SQL + '[tbl15].[Kr_IL.BTS], '
    else set @SQL = @SQL + 'null as [Kr_IL.BTS], '
if @tag2 = 1 set @SQL = @SQL + '[tbl15].[Kr_IL.CS], '
    else set @SQL = @SQL + 'null as [Kr_IL.CS], ';
set @SQL = STUFF(@SQL, len(@SQL), 1, ' from [tbl15]')
exec (@SQL)

This is part of stored procedure script I have a problem with. The message is:

"Msg 156, Level 15, State 1, Procedure SP_select, Line 202 Incorrect syntax near the keyword 'set'.".

If I write a standard select statement (with full set of columns) it works fine. But I need to "control" the columns (real data or null data according enable tags). According error message the error point is:

set @SQL = 'select [tbl15].[DT], '

Thanks in advance.

score 11 · Accepted Answer · answered Jan 28 '13 at 11:42

You're getting this error because you're not referencing the CTE in the query immediately after it (dynamic SQL doesn't count!)

Your variables for controlling the flow could be introduced into the script directly, rather than the need for dynamic SQL, by using a case statement:

SELECT [DT],
       CASE
         WHEN @tag1 = 1 THEN [Kr_IL.BTS]
         ELSE NULL
       END AS BTS,
       CASE
         WHEN @tag2 = 1 THEN [Kr_IL.CS]
         ELSE NULL
       END AS CS
FROM   tbl15

So your whole script becomes:

;WITH tbl1 AS
    (
    SELECT ...
    ),
tbl2 AS
    (
    SELECT ...
    ),
tbl15 AS
    (
    SELECT [tbl1].[DT],
    [tbl1].[Kr_IL.BTS],
    [tbl2].[Kr_IL.CS],
    FROM [tbl1], [tbl2]
    WHERE
    [tbl1].[DT] = [tbl2].[DT]
    AND [tbl1].[DT] = [tbl3].[DT]
    )
SELECT [DT],
       CASE
         WHEN @tag1 = 1 THEN [Kr_IL.BTS]
         ELSE NULL
       END AS BTS,
       CASE
         WHEN @tag2 = 1 THEN [Kr_IL.CS]
         ELSE NULL
       END AS CS
FROM   tbl15

Is there any other way to deal with this error? I would like to be able to use the CTE in a following dynamic query. — Edward, May 13 '17 at 04:01
@Edward I'd suggest asking a new question showing your code and the community will make suggestions! — Bridge, May 22 '17 at 07:41
No need your first sentence says it all. A CTE is more temporary than a temporary. — Edward, May 22 '17 at 08:41

Incorrect syntax after CTE

1 Answers1

Linked