SQL Server FOR EACH Loop

Question

SQL query:

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

Returns : '1/1/2010'.

I want to create a calendar with dates like: 

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

Then I want to FOR EACH through the numbers and run 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 may need to add some sort of union at the conclusion of the query so that each iteration of the loop unions onto the next because I want this to provide the data as a single resultset rather than numerous resultsets. 

Answer 1

Since SQL is largely a set-oriented language, using a loop in it is typically not a good idea.

In this situation, a recursive CTE could produce a comparable outcome:

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

I hope this helps you.