Dynamic SQL Loop

Loops within dynamic SQL

Here is one way of doing it:

--Declare a table variable to hold your table names (and column names in case needed)
declare @listOfTablesToUpdate table (tableName varchar(100), columnNameToUpdate varchar(50))

--insert the tables that you want to work with.
insert into @listOfTablesToUpdate values ('Table1', 'column2')
insert into @listOfTablesToUpdate values ('Table2', 'column3')
insert into @listOfTablesToUpdate values ('Table3', 'column4')

--Cursor for iterating
declare @tableCursor cursor,
        @tableName varchar(100),
        @columnName varchar(50)

set @tableCursor = cursor for select * from @listOfTablesToUpdate

open @tableCursor
fetch next from @tableCursor into @tableName, @columnName
while(@@fetch_status = 0)
begin
    --dynamic sql
    declare @sql varchar(max)

    --Your logic here...this is just an example
    set @sql = 'update '+@tableName+' set '+@columnName+' = '+<value>+' where '+@columnName +' = '+<someothervalue>
    exec @sql

    fetch next from @tableCursor into @tableName, @columnName
end

close @tableCursor
deallocate @tableCursor

T-SQL dynamic sql within while loop

When you use EXEC with a string, the command is carried out in a new session, so variables cannot be used pass arguments or get results. However, you could create a temporary table, put the arguments in it and use this table inside the dynamic statement:

create table #T (val_1 varchar(10), val_2 varchar(10), val_3 varchar(10));
insert into #T values ('abcef', 'ghijk', 'lmnopq');
declare @counter tinyint
set @counter = 1
while @counter<=3
begin
    declare @sql_code varchar(max)
    set @sql_code = '
       declare @v varchar(10);
       select @v = val_' + CONVERT(varchar(10), @counter) + ' FROM #T;
       print @v;
    '
    print @sql_code
    exec (@sql_code)
    set @counter = @counter + 1
end

cursor for loop & dynamic SQL - Snowflake

Instead of concatenating the query string which makes it almost unreadable it could be rewritten using bind variables:

DECLARE
    dt text;
    tbl text;
    stmt text;
    c1 CURSOR FOR SELECT dt, tbl from t;
BEGIN
  FOR record in c1 DO
    dt := record.dt;
    tbl := record.tbl;
    
    stmt := 'INSERT INTO result(cnt, day_of_month)
             SELECT COUNT(*), DAYOFMONTH(IDENTIFIER(?)) AS day_of_month
             FROM TABLE(?)
             WHERE YEAR(IDENTIFIER(?)) = YEAR(CURRENT_DATE)
               AND MONTH(IDENTIFIER(?)) = MONTH(CURRENT_DATE)
             GROUP BY day_of_month';
             
     EXECUTE IMMEDIATE :stmt USING (dt, tbl, dt, dt); 
     
     RETURN stmt;
  END FOR;
END;

If column or table is parameter it should be wrapped with IDENTIFIER/TABLE funtion.

---

For sample data:

CREATE OR REPLACE TABLE t AS
SELECT  'col1' AS dt, 'tab1' AS tbl UNION ALL 
SELECT  'col2' AS dt, 'tab1' ;

CREATE TABLE tab1(col1 DATE, col2 DATE) AS
SELECT CURRENT_DATE(), CURRENT_DATE()-40;

CREATE TABLE result(cnt INT, day_of_month INT);

SELECT * FROM result;

T-SQL - Building Dynamic Query in While Loop

Why you do not see anything is at the last loop @currentTable is null due to the last row has been deleted in the next to last loop. As the @currentTable is null, your concatenation for @joinclauses and @tableSum result in NULL values, and won't print.

I would change your code to something like so:

declare @airflowTablesToSum as varchar(max) = 'FP_1_01RM109CSA_F,FP_1_02RM109ASA_F,FP_1_03RM109DSA_F,FP_1_04RM110SA_F,FP_1_05RM111ASA_F,FP_1_06RM112SA_F,FP_1_07RM108SA_F,FP_1_08MAUTSA_F,FP_1_09SAUTSA_F,FP_1_10SAUTSA_F,FP_1_11MAUTSA_F,FP_1_12RM104SA_F,FP_2_01RM208SA_F,FP_2_02RM207SA_F,FP_2_03RM206SA_F,FP_2_04RM208BSA_F,FP_2_05RM209SA_F,FP_2_06RM209ASA_F,FP_2_07RM205SA_F,FP_2_08RM205ASA_F,FP_2_09RM224SA_F,FP_2_10RM232SA_F,TECHEDFP211RM239SAF,TECHEDFP212RM239ASAF,TECHEDFP213RM204SAF,TECHEDFP214RM213SAF,TECHEDFP215RM215SAF,TECHEDFP216RM219SAF,TECHEDFP217ARM241SAF,TECHEDFP217RM240SAF,TECHEDTU201RM205ESAF,TECHEDTU202RM209BSAF,TECHEDVMA11RM101VSUPFLOW,TECHEDVMA12RM101SSUPFLOW,TECHEDVMA13RM101RSUPFLOW,TECHEDVMA14RM101QBSUPFLOW,TECHEDVMA15RM101PSUPFLOW,TECHEDVMA29RM101ESUPFLOW,TECHEDVMA30RM101KSUPFLOW,TECHEDVMA31RM101JSUPFLOW,TECHEDVMA32RM101SUPFLOW,TU001SAF';

CREATE TABLE #tableNames (id int identity(1,1), tname varchar(max));
insert into #tableNames select value from string_split(@airflowTablesToSum,',');

declare @previousTable varchar(max) = (select tName from #tableNames where id=1);
DELETE FROM #tableNames WHERE tname = @previousTable;

declare @joinClauses varchar(max) = ' ' + @previousTable + ' a ';
declare @tableSum varchar(max) = 'a.[VALUE]';
set @previousTable = 'a';

select * from #tableNames;

declare @currentTable varchar(max);

SELECT TOP(1) @currentTable =  tname FROM #tableNames

WHILE @@ROWCOUNT <> 0
BEGIN

    set @joinClauses += ('join ' + @currentTable + ' on cast(' + @currentTable + '.[TIMESTAMP] as smalldatetime) = cast(' + @previousTable + '.[TIMESTAMP] as smalldatetime) ');
    set @tableSum += (' + ' + @currentTable + '.[VALUE]');

    set @previousTable = @currentTable;
    DELETE FROM #tableNames WHERE tname = @currentTable;

    SELECT TOP(1) @currentTable =  tname FROM #tableNames

END
    RAISERROR(@joinClauses,0,1);
    RAISERROR(@tableSum,0,1);

drop TABLE #tableNames;

Also, why are are you using RAISERROR to in essence PRINT?

Anyway, hope this helps!

Niels

While LOOP IN Dynamic QUERY

In your case you can use two variants:

1) to use CAST or CONVERT function

-- CAST
SET @sqlQuery += 'max(CASE WHEN Cluster_ID = (Select STORE_ID from Table_Name where id= '+CAST(@cnt AS varchar(5))+')
then CAST(c.APS_Dev as decimal(10,2)) end) as ''APS Dev'' '

-- CONVERT
SET @sqlQuery += 'max(CASE WHEN Cluster_ID = (Select STORE_ID from Table_Name where id= '+CONVERT(varchar(5),@cnt)+')
then CAST(c.APS_Dev as decimal(10,2)) end) as ''APS Dev'' '

2) to use CONCAT function for strings concatenation

SET @sqlQuery += CONCAT('max(CASE WHEN Cluster_ID = (Select STORE_ID from Table_Name where id= ',@cnt,')
then CAST(c.APS_Dev as decimal(10,2)) end) as ''APS Dev'' ')

---

Related Topics