Geek asks and answers

Incorrect syntax after CTE

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 the script stored procedure. I have a problem. Message:

"Msg 156, level 15, state 1, procedure SP_select, line 202 invalid syntax next to the keyword 'set'.".

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

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

Thanks in advance.

+5
source share
1 answer

You get this error because you are not referring to the CTE in the query immediately after it (dynamic SQL is not taken into account!)

script, SQL, case:

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

, script :

;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
+10

More articles:


All Articles