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.

Answer 1

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