0
DECLARE @overview TABLE(
    category VARCHAR(50)
)

INSERT INTO @overview
SELECT DISTINCT category
FROM table_3

DECLARE @temp_table TABLE(
    category VARCHAR(100),
    increment IDENTITY(1,1)
)

INSERT INTO @temp_table
SELECT DISTINCT category
FROM table_1

DECLARE @cursor INT
SET @cursor = 2

DECLARE @cursor_max INT
SET @cursor_max = (
    SELECT MAX(increment)
    FROM @temp_table
)

DECLARE @top_cat VARCHAR(100)
SET @top_cat = (
    SELECT TOP(1) category
    FROM @temp_table    
)

DECLARE @select_module NVARCHAR(MAX)
SET @select_module = CONCAT(
    'subfunction, [',@top_cat,']'
)

DECLARE @join_module NVARCHAR(MAX)
SET @join_module = CONCAT(
    '
    LEFT JOIN (
        SELECT SUM(table_2.totals) [',
        (SELECT @top_cat,'],
        table_2.description
        WHERE table_2.category = ',
        (SELECT @top_cat),'
        GROUP BY table_2.description
    ) AS join_',@cursor,'
    ON join_',@cursor,'.description = oo.function'
)

WHILE @cursor <= @cursor_max
BEGIN
    SET @select_module = CONCAT(
        @select_module,', [',(SELECT category from @temp_table WHERE increment = @cursor),']'
    )
    SET @join_module = CONCAT(
        @join_module,'
        LEFT JOIN (
            SELECT SUM(table_2.totals) [',
            (SELECT category from @temp_table WHERE increment = @cursor),'],
            table_2.description
            WHERE table_2.category = ',
            (SELECT category from @temp_table WHERE increment = @cursor),'
            GROUP BY table_2.description
        ) AS join_',@cursor,'
        ON join_',@cursor,'.description = oo.function'
    )
    SET @cursor += 1
END

DECLARE @instruction NVARCHAR(MAX)
SET @instruction = CONCAT(
    'SELECT ',@select_module,'
    FROM ',@overview ,'oo
    ',@join_module
)

EXEC sp_executesql @instruction

The code above gives the following errors:

Must declare the table variable "@overview".
Incorrect syntax near 'Line'.
Incorrect syntax near the keyword 'AS'.
Incorrect syntax near the keyword 'Full'.

I think the errors are coming from string formatting, but I can't place where any mistakes are. I'm trying to join multiple columns onto the table @overview, using the @cursor and while loop to iterate through @temp_table to find the columns to join, and then the @select_module, @join_module, and @instruction to build the final statement. Any help would be really appreciated

Marko Ivkovic
  • 1,262
  • 1
  • 11
  • 14
  • 1
    Try to print variable @instruction and other. When you get result copy it to another tab and try to execute. Step by step and I think that will solve your problem. – Marko Ivkovic Jul 08 '21 at 10:42
  • A look at your SQL, and there's a lot that doesn't make sense, For example you define `@overview` as a table type variable, but in places try to reference the **scalar** variable `@overview`. You have subqueries which don't close their parathesis; for example `(SELECT @top_cat,'],` (why is that not just `@top_cat` anyway?). It's also open to injection as syntax like `'[' + {expression} + ']'` does not safely escape values; you should be using `QUOTENAME`. You need to take a step back here and explain what you are really trying to achieve as the SQL above is littered with errors. – Thom A Jul 08 '21 at 10:56
  • I see a few mistakes: @temp_table doesn't have a data type for increment (identity isn't a data type). As mentioned by others, `@join_module` has mismatched open and close parenthesis, particularly at the first (SELECT `@top_cat`; additionally, unsure why there's a select there. That is messing up the WHILE loop. get through these and then print your statement, as Marko mentioned and you should see what your other issues may be – Eli Jul 08 '21 at 13:22
  • wrt. `Must declare the table variable "@overview".` `sp_executesql` will be executing `@instruction` in its own scope so it won't see the `@overview` table. Consider converting that to a connection-specific temp table, `#overview`. Also, instead of trying to quote your own tokens with `[` and `]` use the [QUOTENAME()](https://learn.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql) function. – AlwaysLearning Jul 09 '21 at 02:29

0 Answers0