SQL Server FOR EACH Loop

Question

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.

Can you explain why you need to do this? 95% of the time when you need a looping structure in tSQL you are probably doing it wrong. — JohnFx, Apr 24 '12 at 14:53
Why not create a table where you can populate the dates you want to run it against that. There is almost certainly a better way to do this than looking through hardcoded constant values. — JohnFx, Apr 24 '12 at 14:57
The dates in your example are sequential by month. Is that a rule, or do you need to be able to run for an arbitrary set of dates? Is there a reason that you can't edit the large query to take a date range or set of dates rather than a single date? If you absolutely need to step through iterations (against the good advice given above), then you might want to consider using a cursor. — JAQFrost, Apr 24 '12 at 22:36

score 72 · Accepted Answer · 2018-08-29T08:59:55.367

72

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

edited Aug 29 '18 at 08:59

answered Apr 24 '12 at 14:57

8

Maximum step of `i` is limited to 100 which is equal to max recursion limit. Try `... from CTE where i <= 101` Or incread recursion limit adding by `OPTION (MAXRECURSION 500)` – guneysus Jul 11 '16 at 12:23
8

Am I the only one who didn't understand this accepted answer? – Tk1993 Jun 21 '18 at 11:26
@Tk1993: Which bit is unclear? The (recursive) CTE produces a set of numbers from 1 to 5, while the `select dateadd` part of the query adds each of those numbers (minus 1) as days to the 1st January 2010. Or is it my aversion to using a loop in SQL? – Jun 21 '18 at 19:06
@ChrisW: The OP is based in the UK, where dates are formatted as dmy, not mdy (as in the USA). – Aug 29 '18 at 09:01

Lamak · Answer 2 · 2012-04-24T15:22:23.207

37

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.

edited Apr 24 '12 at 15:22

answered Apr 24 '12 at 14:54

Lamak

69,480
12
108
116

I need to loop dates not integers, see edited question. Would a WHILE loop work with dates ? – JsonStatham Apr 24 '12 at 15:12
@SelectDistinct - Yes, it should work just fine. I changed my answer to returns dates instead of ints – Lamak Apr 24 '12 at 15:22
1

Mark Bannister's answer is less code and more efficient. There's no reason to use a while loop for something like this. – sorpigal Apr 24 '12 at 16:41

score 17 · Answer 3 · edited Feb 16 '15 at 21:46

17

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

edited Feb 16 '15 at 21:46

guneysus

6,203
2
45
47

answered Apr 24 '12 at 14:55

Steve Mayne

22,285
4
49
49

score 8 · Answer 4 · answered Apr 24 '12 at 14:57

8

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)

answered Apr 24 '12 at 14:57

Mark Brackett

84,552
17
108
152

Interesting that you consider this to be a "set-based option" (the word `RECURSION` is a bit of a give away!) – onedaywhen Apr 25 '12 at 08:17
@onedaywhen - there's no contradiction there. A recursive CTE *is* set-based. The anchor member (SELECT 1) is set S[0], which is then UNION ALL'ed with n more sets (SELECT 1 + N FROM S[n - 1]) until an empty set is encountered. The result is the UNION of sets S[0] to S[n]. That being said - I personally prefer a physical table as it's more efficient. – Mark Brackett Apr 25 '12 at 13:52
[Joe Celko's thoughts on the matter](http://www.simple-talk.com/sql/t-sql-programming/procedural,-semi-procedural-and-declarative-programing-part-ii/): "it makes the semi-procedural programmer feel good to use [a recursive] CTE... But recursion is actually a procedural technique. It is also expensive since it is really a cursor under the covers" -- I'm not saying he is correct but demonstrates the position isn't as clear cut as you would have it. Not a criticism of your comments either. As I said, I find it interesting without holding strong views on the subject myself :) – onedaywhen Apr 26 '12 at 07:26
@onedaywhen - And [here](http://books.google.com/books?id=8y823V4-5ioC&pg=PA683&lpg=PA683&dq=is+recursive+cte+%22procedural%22&source=bl&ots=Yo4z6upDSR&sig=wh3zT4NmJiNs8_Bea2x8WBCp1Qg&hl=en&sa=X&ei=8F2ZT7m8Do-e8QSouKibBg&ved=0CEsQ6AEwBA#v=onepage&q=is%20recursive%20cte%20%22procedural%22&f=false), Celko makes a distinction between "procedural code...a procedure or a cursor" and a recursive CTE, which "[is] still procedural under the covers". I don't doubt that recursive CTEs are procedurally executed...but so are JOINs. At least we can agree that a numbers table is a better option. – Mark Brackett Apr 26 '12 at 14:43

score 5 · Answer 5 · edited Sep 06 '13 at 18:40

5

declare @counter as int
set @counter = 0
declare @date as varchar(50)
set @date = cast(1+@counter as varchar)+'/01/2013'
while(@counter < 12)
begin 
select  cast(1+@counter as varchar)+'/01/2013' as date
set @counter = @counter + 1
end

edited Sep 06 '13 at 18:40

Jamie Kitson

3,973
4
37
50

answered Jul 12 '13 at 16:22

younes

51
1
2

Shakeer Mirza · Answer 6 · 2019-04-10T13:39:01.600

2

Off course an old question. But I have a simple solution where no need of Looping, CTE, Table variables etc.

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

SELECT DATEADD (DD,NUMBER,@MyVar) 
FROM master.dbo.spt_values 
WHERE TYPE='P' AND NUMBER BETWEEN 0 AND 4 
ORDER BY NUMBER

Note : spt_values is a Mircrosoft's undocumented table. It has numbers for every type. Its not suggestible to use as it can be removed in any new versions of sql server without prior information, since it is undocumented. But we can use it as quick workaround in some scenario's like above.

edited Apr 10 '19 at 13:39

answered Mar 24 '17 at 06:58

Shakeer Mirza

5,054
2
18
41

spt_values is a Mircrosoft's undocumented table. It has numbers for every type. Its not suggestible to use as it can be removed in any new versions of sql server without prior information, since it is undocumented. But we can use it as quick workaround in some scenario's like above. @MattE – Shakeer Mirza Mar 25 '19 at 05:55

score 1 · Answer 7 · edited Mar 19 '15 at 12:39

[CREATE PROCEDURE [rat].[GetYear]

AS
BEGIN

-- variable for storing start date
Declare @StartYear as int
-- Variable for the End date 
Declare @EndYear as int 

-- Setting the value in strat Date
select @StartYear = Value from   rat.Configuration where Name = 'REPORT_START_YEAR'; 

-- Setting the End date 
select @EndYear = Value from   rat.Configuration where Name = 'REPORT_END_YEAR'; 


-- Creating Tem table 
    with [Years] as
    (
        --Selecting the Year
        select @StartYear [Year] 
        --doing Union 
        union all
         -- doing the loop in Years table 
         select Year+1 Year from [Years] where Year < @EndYear
     )
    --Selecting the Year table 
selec]

SQL Server FOR EACH Loop

7 Answers7

Linked