Nested While loops in SQL?

Question

I want to change my SQL code which generates n table using two nested While loops, like:

DECLARE @count1 INT
SET @count1 = 2012
 DECLARE @count2 INT
 SET @count2 = 1
 WHILE @count1 <= 2016
 BEGIN
WHILE @count2 <= 12
  create table LGDfigRecov as
select   ...
from ...
WHERE FD0.mo_id=count2
   AND FD0.an_id= count1
...
SET @count2 += 1
END
SET @count1 += 1
END

How can I change every time the name of new table like "LGDfigRecov +count1+count2"? It means I want to create every time a new table with the name of year and month at the end.

Dynamic SQL - meaning you build a string of your sql command in sql and then execute the string. — Allan S. Hansen
– Allan S. Hansen, Commented Sep 1, 2016 at 12:33
How about one table with columns containing count1, count2 — Alex K.
– Alex K., Commented Sep 1, 2016 at 12:33
First of all SQL SERVER does not allow to create a table like this create table LGDfigRecov as select ... you need dynamic query — Pரதீப்
– Pரதீப், Commented Sep 1, 2016 at 12:33
It really sounds like you need one table with a column that stores the year and month — Lamak
– Lamak, Commented Sep 1, 2016 at 12:34
I think this would be a good entry point: sqlteam.com/article/introduction-to-dynamic-sql-part-1 .Especially the last example (it's an old article though so look up sql flavour changes to your specific version) — Allan S. Hansen
– Allan S. Hansen, Commented Sep 1, 2016 at 12:40

Gottfried Lesigang · Accepted Answer · 2016-09-01 13:32:30Z

1

You could use a query like this to create your statements.

WITH 
MonthNumbers AS (SELECT * FROM(VALUES('01'),('02'),('03'),('04'),('05'),('06'),('07'),('08'),('09'),('10'),('11'),('12')) AS x(MonthNr))
,YearNumbers AS  (SELECT * FROM(VALUES('2012'),('2013'),('2014'),('2015'),('2016')) AS x(YearNr))
SELECT ROW_NUMBER() OVER(ORDER BY YearNr,MonthNr) AS SortInx
      ,CONCAT('CREATE TABLE LGDfigRecov_',YearNr,'_',MonthNr,' AS ', CHAR(13) + CHAR(10)) +
       CONCAT('SELECT ... FROM ... ', CHAR(13) + CHAR(10)) + 
       CONCAT('WHERE FD0.mo_id=',MonthNr,' AND FD0.an_id=',YearNr,';') AS Cmd
FROM MonthNumbers
CROSS JOIN YearNumbers

I always try to avoid unnecessary loops and procedural approaches...

Check them if they are valid syntax (just copy the output in a new query window)
Open a CURSOR from this SELECT
Use the CURSOR to fetch this row-wise into a variable @Cmd
Use EXEC (@Cmd)

answered Sep 1, 2016 at 13:32

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

maniA Over a year ago

I get unfortunately the following error: ERROR : Statement is not valid or it is used out of proper order.

Gottfried Lesigang Over a year ago

Try to copy the whole result-set into a new query window and check for syntax errors. You must - of course - replace the SELECT ... FROM ... with proper approproiate column and table names... When implementing the CURSOR Id advise to add a PRINT @Cmd and out-comment the EXEC(@Cmd). This way you can re-check the statements which are going to be executed.

Ganesh · Accepted Answer · 2016-09-01 21:25:02Z

1

Below code will help to achieve your goal.

DECLARE @count1 INT, @w_SQL nvarchar(4000); SET @count1 = 2012 DECLARE @count2 INT SET @count2 = 1 WHILE @count1 <= 2016 BEGIN WHILE @count2 <= 12 SET @w_SQL = 'create table LGDfigRecov' + CONVERT(nvarchar(10), @count1) + CONVERT(nvarchar(10), @count2) + 'as select ... from ... WHERE FD0.mo_id=count2 AND FD0.an_id= count1 ...' EXEC sp_executesql @w_SQL SET @count2 += 1 END SET @count1 += 1

answered Sep 1, 2016 at 21:25

Ganesh

266 bronze badges

1 Comment

maniA Over a year ago

it dose not work, probably because I am using the sql within SAS. However, thanks!

Collectives™ on Stack Overflow

Nested While loops in SQL?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related