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

Not sure what you are doing with the third part, you need to execute strSQL2 and then add it as table name to strSQL1 which would make your query as select * from "name generated from subquery result". — Vinay Pandey, Feb 19 '16 at 13:24
An important tool in working with dynamic sql is the PRINT statement. PRINT the sql string(s) that you are trying to execute, and usually the syntax errors will be obvious. — Tab Alleman, Feb 19 '16 at 13:28
What I need is "select ---- from .FT_TBL" for all schemas. I need to have the complete result-set saved in a table at the end of the execution. — Triumph Spitfire, Feb 19 '16 at 13:45
`strSQL2` should not be a dynamic query. if you run it and concat `strSQL1` with `TABLE_SCHEMA` you will finally obtain full script with all tables. After a single run of `strSQL2`. — Ivan Starostin, Feb 19 '16 at 13:50
without row_number and @intcnt of course. which will be useless in this case. — Ivan Starostin, Feb 19 '16 at 13:53
@IvanStarostin, thanks for your reply. I may be overlooking something very obvious here, but do you mind showing how this can be done for all schemas without a loop and counter? — Triumph Spitfire, Feb 19 '16 at 14:09
check out `select` in answer: http://stackoverflow.com/questions/887628/convert-multiple-rows-into-one-with-comma-as-separator — Ivan Starostin, Feb 19 '16 at 14:13

jclozano · Answer 1 · 2016-02-19T15:28:58.343

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

edited Feb 19 '16 at 15:28

answered Feb 19 '16 at 15:22

jclozano

618
6
24

Thank you so much for your reply. I'm on a Parallel data warehouse and cursors are not supported in it :( – Triumph Spitfire Feb 19 '16 at 15:48
Though i hope it can be useful for you even if only as a basis for knowing what to look for. – jclozano Feb 19 '16 at 22:15
I found that in parallel you must use the table as a queue instead of cursor http://rusanu.com/2010/03/26/using-tables-as-queues/ – jclozano Feb 19 '16 at 22:17

Yuval Perelman · Answer 2 · 2016-02-19T15:06:34.033

0

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'

edited Feb 19 '16 at 15:06

answered Feb 19 '16 at 13:21

Yuval Perelman

4,499
1
22
32

what parse error?, if it's the '' i fixed it, but i cant really test it – Yuval Perelman Feb 19 '16 at 15:05
Thanks for that. Should I be using a dynamic sql here? When I executed your select statement (without any variables), I get: Msg 103010, Level 16, State 1, Line 1 Parse error at line: 14, column: 1: Incorrect syntax near '.'. – Triumph Spitfire Feb 19 '16 at 15:41
the statement i have written is what you are executing, its not for you to execute. below i have written how to correct this, remove the '.' from your query – Yuval Perelman Feb 19 '16 at 19:52

Execute Dynamic Query in a Loop in SQL

2 Answers2