How While loop in SQL works

Question

I am working with While loop in sql and I have tried applying code on these dates.

DECLARE @StartDate DATETIME = '2018-08-01';
DECLARE @EndDate DATETIME = '2018-08-15';

WHILE @StartDate < @EndDate

BEGIN

SELECT @StartDate 
SET @StartDate = @StartDate+1

END;

When I run this I get the output in multiple windows though I want it to be in same window.

Create a temp table and do an insert on each iteration, when the loop exits, do a select of your temp table. — Ryan Wilson, Aug 15 '18 at 13:49
for reference: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/while-transact-sql?view=sql-server-2017 — Md. Tazbir Ur Rahman Bhuiyan, Aug 15 '18 at 13:50
What are you actually trying to accomplish? This smells like an XY problem (http://xyproblem.info/) — Brian, Aug 15 '18 at 13:51
Do not use + to add dates... this method only works sometimes and you're going to get unexpected results eventually. i.e. if you use another date type, or you want to add something other than a day. use `dateadd` always, to avoid this. — S3S, Aug 15 '18 at 13:57
Possible duplicate of [Do while loop in SQL Server 2008](https://stackoverflow.com/questions/4487546/do-while-loop-in-sql-server-2008) — S3S, Aug 15 '18 at 13:58
Generally, we try *not* to write loops. SQL is "set-oriented", most of the tools for optimizing queries, etc, assume you'll be writing set-oriented code. We want to tell the system "this is how you should process *all* rows" and let it work out how best it should *deliver* that to you. — Damien_The_Unbeliever, Aug 15 '18 at 13:58
As an example, the set-based way of approaching this may assume that you've already built a *calendar* table in your database (one row per day, with columns for interesting things you may want to know for each day, such as whether your organisation considers it to be a working day). You'd then just `SELECT` from this table where the date lies between your two endpoints. (Many people have a gut reaction to the thought of a calendar table - "wouldn't it end up containing a lot of rows?"- without realising the 20 years is less than 10000. Quite small) — Damien_The_Unbeliever, Aug 15 '18 at 14:01
I would use a tally table for this if you don't have a calendar table. But I agree with @Damien_The_Unbeliever that many people think it would be bad and avoid them for reasons that are not true. — Sean Lange, Aug 15 '18 at 14:23

score 4 · Answer 1 · answered Aug 15 '18 at 13:51

4

Your loop is making a select, so each iteration of the loop will provide a new query results window. If you want it to come back as one result set, you will have to insert it into a table and select all outside of the loop.

DECLARE @StartDate DATETIME = '2018-08-01';
DECLARE @EndDate DATETIME = '2018-08-15';
DECLARE @table TABLE (yourdate DATETIME)
WHILE @StartDate < @EndDate

BEGIN

INSERT INTO @table (yourdate)
SELECT @startdate 
SET @StartDate = @StartDate+1

END;

SELECT * 
FROM @Table

answered Aug 15 '18 at 13:51

dfundako

8,022
3
18
34

1

This answer actually addresses the "why does this happen" question posed by the OP, rather than jumping right to "here's how you should do this instead." +1 for starting where the poster is. – Eric Brandt Aug 15 '18 at 14:37

Yogesh Sharma · Answer 2 · 2018-08-15T14:16:17.047

3

You can use recursive way with common_table_expression instead :

DECLARE @StartDate DATETIME = '2018-08-01';
DECLARE @EndDate DATETIME = '2018-08-15';

WITH t AS (
     SELECT @StartDate AS Startdate
     UNION ALL
     SELECT DATEADD(DAY, 1, Startdate)
     FROM t
     WHERE Startdate < @EndDate 
)

SELECT Startdate
FROM t;

edited Aug 15 '18 at 14:16

answered Aug 15 '18 at 13:53

Yogesh Sharma

49,870
5
26
52

6

Since recursive CTEs can be tricky to get your head around, a brief explanation or link to an introduction might be useful to understand how this works. – IMSoP Aug 15 '18 at 13:56
2

This looks like a set based approach but really using a recursive cte like this is still RBAR. Check out this article on this. http://www.sqlservercentral.com/articles/T-SQL/74118/ – Sean Lange Aug 15 '18 at 14:21

Prashant Pimpale · Answer 3 · 2018-08-16T03:09:43.633

Try with #temp table, because whenever that while loop running you are selecting a new SELECT statement, that is why it comes in a different window. To get the all result in one window you could try CTE or #temp table.

DECLARE @StartDate DATETIME = '2018-08-01';
DECLARE @EndDate DATETIME = '2018-08-15';

CREATE TABLE #Temp 
(
Date date
)

WHILE @StartDate < @EndDate

BEGIN

INSERT INTO #Temp VALUES (@StartDate)

SET @StartDate = @StartDate+1

END;

SELECT * FROM #Temp

score 0 · Answer 4 · edited Jan 23 '23 at 07:43

0

Try this SQL,

DECLARE @StartDate as datetime
DECLARE @EndDate as datetime

set @StartDate = '2018-01-01'
set @endDate = '2022-12-31'


CREATE TABLE #Temp  
-- columns

delete from #TEMP
WHILE @StartDate < @EndDate

BEGIN

INSERT INTO #Temp VALUES (@StartDate)

SET @StartDate = @StartDate+1

END;

SELECT * FROM #Temp

edited Jan 23 '23 at 07:43

Viktor Ivliiev

1,015
4
14
21

answered Jan 21 '23 at 08:49

Chokoh Parulian

1
1

How While loop in SQL works

4 Answers4