SQL Server FOR EACH Loop

Question

I am having the below SQL query:

DECLARE @MyVar datetime = '1/1/2010'    
SELECT @MyVar

This normally returns '1/1/2010'.

What I need to do is have a list of dates, say:

1/1/2010
2/1/2010
3/1/2010
4/1/2010
5/1/2010

Then I need to FOR EACH through the numbers and execute the SQL Query.

Something like (pseudocode):

List = 1/1/2010,2/1/2010,3/1/2010,4/1/2010,5/1/2010

For each x in List
do
  DECLARE @MyVar datetime = x

  SELECT @MyVar

So this would return:-

1/1/2010 2/1/2010 3/1/2010 4/1/2010 5/1/2010

I need this to return the data as one resultset, not the multiple resultsets, so I may want to use some sort of union at the end of the query, so every iteration of the loop unions onto the next.

Answer 1

SQL is primarily a set-orientated language - it is generally a bad idea to use a loop in it.

In this case, the same result could be achieved using a recursive CTE:

with cte as
(select 1 i union all
 select i+1 i from cte where i < 5)
select dateadd(d, i-1, '2010-01-01') from cte

Want to know more about SQL ? Join this SQL Certification course by Intellipaat.

If you want to learn SQL, refer to the below SQL tutorial video: