# Variable in WHILE loop Syntax

**URL:** <https://forum.inductiveautomation.com/t/variable-in-while-loop-syntax/71051>\
**Category:** General Discussion\
**Created:** [March 14, 2023, 8:16pm UTC](https://forum.inductiveautomation.com/t/variable-in-while-loop-syntax/71051 "2023-03-14T20:16:29Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![vasilevdim](https://avatars.discourse-cdn.com/v4/letter/v/7ba0ec/32.png) [@vasilevdim](https://forum.inductiveautomation.com/u/vasilevdim)\
**Post date:** [March 14, 2023, 8:16pm UTC](https://forum.inductiveautomation.com/t/variable-in-while-loop-syntax/71051/1 "2023-03-14T20:16:29Z")

</div>

Hi,  
I try to write SQL query using variable concatenated to the table column names in while loop. Lets say I have 20 Counters from Counter1 to Counter20 and I want to write SELECT statement:

```auto
DECLARE @count INT;
SET @count = 1;
WHILE @count<= 20
BEGIN
   SELECT Counter(@count) FROM Outputs
   SET @count = @count + 1;
END;

```

Of course, it throws error. What is the correct syntax in this case?

---

<div class="post-metadata">

**Author:** ![zacht](https://avatars.discourse-cdn.com/v4/letter/z/ea5d25/32.png) [@zacht](https://forum.inductiveautomation.com/u/zacht)\
**Post date:** [March 14, 2023, 8:33pm UTC](https://forum.inductiveautomation.com/t/variable-in-while-loop-syntax/71051/2 "2023-03-14T20:33:12Z")

</div>

What does the table itself look like? Does every counter have its own column? How many rows do you expect to return?

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [March 14, 2023, 8:53pm UTC](https://forum.inductiveautomation.com/t/variable-in-while-loop-syntax/71051/3 "2023-03-14T20:53:31Z")

</div>

This is SQL script, not a SQL statement. Only a few JDBC drivers permit SQL scripts. On top of that, it returns multiple one-row result sets. Ignition cannot handle that, either.

Consider using a recursive Common Table Expression.

---

<div class="post-metadata">

**Author:** ![vasilevdim](https://avatars.discourse-cdn.com/v4/letter/v/7ba0ec/32.png) [@vasilevdim](https://forum.inductiveautomation.com/u/vasilevdim)\
**Post date:** [March 20, 2023, 4:48pm UTC](https://forum.inductiveautomation.com/t/variable-in-while-loop-syntax/71051/4 "2023-03-20T16:48:07Z")

</div>

OK, in order to close this thread here is the query:

```auto
IF OBJECT_ID('tempdb.dbo.#ResultRooms', 'U') IS NOT NULL
    DROP TABLE #ResultRooms; CREATE TABLE #ResultRooms (
    DateKpi DATE
    ,Room VARCHAR(512)
    ,KPIA REAL
    ,KPIP REAL
    ,KPI_Room REAL
    ) DECLARE @Counter INT SET @Counter = 1 WHILE (@Counter <= 20)
BEGIN
    EXEC (
            'INSERT INTO #ResultRooms 
        SELECT CAST(t_stamp AS DATE) AS DateKpi
        , MAX(Room' + @Counter + ') Room
        , Max(KPIA' + @Counter + ' ) AS KPIA
        , Max(PlanExe' + @Counter + ') AS KPIP
        , Max(KPI_Room' + @Counter + ') KPI_Room
    FROM KPI
    WHERE YEAR(t_stamp)=YEAR(getdate()) AND MONTH(t_stamp)=MONTH(getdate())
    GROUP BY CAST(KPI.t_stamp AS DATE)
    ORDER BY CAST(KPI.t_stamp AS DATE)'
            ) SET @Counter = @Counter + 1
END SELECT *
FROM #ResultRooms

```

I can run successfully the query on the named query test environment, but it does not work in Database query browser or Script console:

```auto
resultsKPI = system.db.runNamedQuery("Tableting_Factory1","TestRecursive", {})
resultsKPI = system.dataset.toDataSet(resultsKPI)
spreadsheet = system.dataset.dataSetToExcel(1, [resultsKPI])
filePathoutputs = "E:\\SCADA\\Tableting_Reports\\KPI_Complete_Data.xls"
system.file.writeFile(filePathoutputs, spreadsheet)

```

What mistake I am doing?

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [March 20, 2023, 5:01pm UTC](https://forum.inductiveautomation.com/t/variable-in-while-loop-syntax/71051/5 "2023-03-20T17:01:37Z")

</div>

JDBC requires queries to be declared as result-returning (SELECT and friends) or status-returning (UPDATE and friends). A named query can be configured one way or the other. Script functions specify as part of the function name which form to use. The query browser has to guess which form to use, and does so with a very simplistic look into the SQL looking for SELECT. You don't have simple syntax.

You still have a SQL script, not a SQL statement, so you are just begging for trouble.