Avoiding while loops in SQL when a counter is required

Question

I feel like this is a common problem, but it seems that none of the answers that I have found on SO or other sites seem to address the issue of a while loop with a counter.

Let's say that I am trying to write a stored procedure in SQL that will populate a user's timesheet by inserting a row for each day for the remainder of the month. If the @endMonth variable holds the last day of the month, then I know that I could easily write a while loop and do something along these lines:

WHILE @date <= @endMonth
BEGIN
    //Do some action with the date, like an insert
    SET @date = DATEADD(d, 1, @date) //increment the date by one day
END

However, looking at answers here and on other sites leads me to believe that it would be best to avoid using a while loop if at all possible.

So my question is this: is there a way I can implement a loop with a counter in SQL without using the WHILE structure? What technique would I use to go about converting a loop similar to the one I posted? Or with something like this, do I have to bite the bullet and just use a while loop?

As an aside, some of the following questions come close, but none of them seem to quite address the issue of needing a counter as a loop condition. Most of the answers seem to condemn using WHILE loops, but I can't seem to find a general purpose solution as to an alternative.
sql while loop with date counter
SQL Server 2008 Insert with WHILE LOOP (this one was close, but unfortunately for me it only works with an auto increment column)

In that secnario, if the missing code was to insert a record for each day, then you could do by joining to a CTE that found missing dates. A general case to get rid of a while loop, notable by the exceptions I should think... Plenty of CTE examples to generate a range of dates on here by the way, usually for counting working days etc. — Tony Hopkinson, Jun 02 '14 at 16:42
This has been a question I have always had. I personally still use while loops when I can't get around it in SQL even though I have been told not to. Nobody has ever provided a concrete reason why its bad. — logixologist, Jun 02 '14 at 17:12
It's bad because it takes longer and uses more resources. SQL is a set based language. — Dave.Gugg, Jun 02 '14 at 17:15
For getting all dates inside some timespan calendar table is often used (which contains one record per day; ours starts at 1990 and ends at 2050 or so) - this way you can just join to calendar table with date condition (datefield between ... or smtg). — Arvo, Nov 24 '22 at 12:43

score 0 · Answer 1 · answered Jun 02 '14 at 16:48

0

As a general case, you can increment values without using cursors by assigning values and incrementing the variable in the same select, like this:

DECLARE @i INT = 0

DECLARE @table TABLE
    (
      ID INT ,
      testfield VARCHAR(5)
    )

INSERT  INTO @table
        ( testfield )
VALUES  ( 'abcd'),
        ( 'efgh' ),
        ( 'ijkl' ),
        ( 'mnop' )


UPDATE  @table
SET     @I = ID = @i + 1

SELECT  *
FROM    @table

answered Jun 02 '14 at 16:48

Dave.Gugg

6,561
3
24
43

1

That is an interesting trick, but I'm not sure that I understand how one would use this to replace a while loop. – ControlAltDeplete Jun 02 '14 at 17:06

Darka · Accepted Answer · 2014-06-02T17:33:36.567

0

I saw many examples of populating data.

First you create dates from starting to ending dates in cte and then you can insert it into table.

One of them is with cte:

DECLARE @StartDate DateTime = '2014-06-01'
DECLARE @EndDate DateTime = '2014-06-29'



;WITH populateDates (dates) AS (

    SELECT @StartDate as dates
    UNION ALL
    SELECT DATEADD(d, 1, dates)
    FROM populateDates
    WHERE DATEADD(d, 1, dates)<=@EndDate

)
SELECT *
INTO dbo.SomeTable
FROM populateDates

You should try to look for on internet how to populate date in sql table

edited Jun 02 '14 at 17:33

answered Jun 02 '14 at 17:27

Darka

2,762
1
14
31

While I was hoping for a more general solution for loops with counters, this one seems like it will work well for a date counter. Though I have to admit, I am torn between the apparent speed benefits of a CTE and the readability of a while loop. In any case, thank you. – ControlAltDeplete Jun 02 '14 at 18:22

score 0 · Answer 3 · answered Nov 24 '22 at 12:37

0

I used a sequence - create temporarily.
I needed to do my updates outside of script context, with plain SQL, sequence was the only "counter" I could come up with.

answered Nov 24 '22 at 12:37

Frischling

2,100
14
34

Avoiding while loops in SQL when a counter is required

3 Answers3