SQL Server FOR EACH Loop

I have the following SQL query:

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

This naturally returns '1/1/2010'.

What I want 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 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 want this to return the data as one resultset, not multiple resultsets, so I may need to use some kind of union at the end of the query, so each iteration of the loop unions onto the next.

edit

I have a large query that accepts a 'to date' parameter, I need to run it 24 times, each time with a specific to date which I need to be able to supply (these dates are going to be dynamic) I want to avoid repeating my query 24 times with union alls joining them as if I need to come back and add additional columns it would be very time consuming.

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

In this case, a similar 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

Here is an option with a table variable:

DECLARE @MyVar TABLE(Val DATETIME)
DECLARE @I INT, @StartDate DATETIME
SET @I = 1
SET @StartDate = '20100101'

WHILE @I <= 5
BEGIN
    INSERT INTO @MyVar(Val)
    VALUES(@StartDate)

    SET @StartDate = DATEADD(DAY,1,@StartDate)
    SET @I = @I + 1
END
SELECT *
FROM @MyVar

You can do the same with a temp table:

CREATE TABLE #MyVar(Val DATETIME)
DECLARE @I INT, @StartDate DATETIME
SET @I = 1
SET @StartDate = '20100101'

WHILE @I <= 5
BEGIN
    INSERT INTO #MyVar(Val)
    VALUES(@StartDate)

    SET @StartDate = DATEADD(DAY,1,@StartDate)
    SET @I = @I + 1
END
SELECT *
FROM #MyVar

You should tell us what is your main goal, as was said by @JohnFx, this could probably be done another (more efficient) way.

You could use a variable table, like this:

declare @num int

set @num = 1

declare @results table ( val int )

while (@num < 6)
begin
  insert into @results ( val ) values ( @num )
  set @num = @num + 1
end

select val from @results

This kind of depends on what you want to do with the results. If you're just after the numbers, a set-based option would be a numbers table - which comes in handy for all sorts of things.

For MSSQL 2005+, you can use a recursive CTE to generate a numbers table inline:

;WITH Numbers (N) AS (
    SELECT 1 UNION ALL
    SELECT 1 + N FROM Numbers WHERE N < 500 
)
SELECT N FROM Numbers
OPTION (MAXRECURSION 500)

SQL Server FOR EACH Loop

Related

Recent Posts