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
– Ryan Wilson, Commented Aug 15, 2018 at 13:49
What are you actually trying to accomplish? This smells like an XY problem (xyproblem.info) — Brian
– Brian, Commented Aug 15, 2018 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
– S3S, Commented Aug 15, 2018 at 13:57
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
– Damien_The_Unbeliever, Commented Aug 15, 2018 at 14:01

dfundako · Accepted Answer · 2018-08-15 13:51:10Z

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, 2018 at 13:51

dfundako

8,3643 gold badges21 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Eric Brandt Over a year ago

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.

Yogesh Sharma · Accepted Answer · 2018-08-15 14:16:17Z

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, 2018 at 14:16

answered Aug 15, 2018 at 13:53

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

2 Comments

IMSoP Over a year ago

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.

Sean Lange Over a year ago

This looks like a set based approach but really using a recursive cte like this is still RBAR. Check out this article on this. sqlservercentral.com/articles/T-SQL/74118

Prashant Pimpale · Accepted Answer · 2018-08-16 03:09:43Z

1

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

edited Aug 16, 2018 at 3:09

answered Aug 15, 2018 at 13:55

Prashant Pimpale

10.7k10 gold badges52 silver badges88 bronze badges

Comments

Viktor Ivliiev · Accepted Answer · 2023-01-23 07:43:55Z

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, 2023 at 7:43

Viktor Ivliiev

1,3545 gold badges17 silver badges26 bronze badges

answered Jan 21, 2023 at 8:49

Chokoh Parulian

11 bronze badge

Collectives™ on Stack Overflow

How While loop in SQL works

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related