Execute Dynamic Query in a Loop in SQL

Question

I am trying to execute a dynamic query inside a while loop in SQL/PDW. This query will give me the max/min dates and also the distinct count of dates from a Fact table. I need to execute this for all schemas in my database (all schemas have the same table structure). The query that I am trying to execute is given below:

DECLARE @intCNT INT
SET @intCNT = 1
DECLARE @strSQL1 VARCHAR(1000)
DECLARE @strSQL2 VARCHAR(1000)
DECLARE @strSQL3 VARCHAR(100)


SET @strSQL1 = 'SELECT MAX(FT_DT) AS MAX_DT, 
                    MIN(FT_DT) AS MIN_DT,  
                    COUNT(DISTINCT FT_DT) AS DT_CNT 
                FROM '

SET @strSQL2 = CONCAT('(SELECT TABLE_SCHEMA
                        FROM 
                            (SELECT ROW_NUMBER() OVER(ORDER BY TABLE_SCHEMA) AS ROW_NUM, 
                                TABLE_SCHEMA
                            FROM INFORMATION_SCHEMA.TABLES
                            WHERE TABLE_NAME = ''FT_TBL''
                                AND TABLE_SCHEMA LIKE ''F%'')T
                        WHERE ROW_NUM = ',@intCNT,')' )

SET @strSQL3 = '.FT_TBL'

EXEC (@strSQL1 + @strSQL2 + @strSQL3)

When I execute this, I get the following error:

Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '.'.

Is it something to do with syntax? I'd like to get this working on my PDW instance. I tried in SQL Server too, but get the same error

Answer 1

Your code can be replaced with a cursor with the following query to enable you to select for each schema

DECLARE @Schema_Name VarChar

DECLARE MY_CURSOR CURSOR 
  LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR 
SELECT DISTINCT TABLE_SCHEMA
FROM TABLE_SCHEMA
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'FT_TBL'
AND TABLE_SCHEMA LIKE 'F%'

OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @Schema_Name 
WHILE @@FETCH_STATUS = 0
BEGIN 
    --Do something with Id here
    execute CONCAT(CONCAT('SELECT MAX(FT_DT) AS MAX_DT, 
                MIN(FT_DT) AS MIN_DT,  
                COUNT(DISTINCT FT_DT) AS DT_CNT 
            FROM ',@Schema_Name),'FT_TBL')
    FETCH NEXT FROM MY_CURSOR INTO @Schema_Name
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

Answer 2

what you are actually trying to excecute is:

SELECT MAX(FT_DT) AS MAX_DT, 
                    MIN(FT_DT) AS MIN_DT,  
                    COUNT(DISTINCT FT_DT) AS DT_CNT 
                FROM 
(SELECT TABLE_SCHEMA
                        FROM 
                            (SELECT ROW_NUMBER() OVER(ORDER BY TABLE_SCHEMA) AS ROW_NUM, 
                                TABLE_SCHEMA
                            FROM INFORMATION_SCHEMA.TABLES
                            WHERE TABLE_NAME = 'FT_TBL'
                                AND TABLE_SCHEMA LIKE 'F%')T
                        WHERE ROW_NUM = 1)
.FT_TBL

the .FT_TBL from @strSQL3 destroy your query. maybe it should be

set @strSQL3 = ' FT_TBL'