CODEWITHSUNDEEP
sqlsql-server
Doonie Darkoo
Doonie Darkoo

Reputation: 1495

How While loop in SQL works

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.

enter image description here

Upvotes: 2

Views: 1192

Answers (4)

Chokoh Parulian
Chokoh Parulian

Reputation: 1

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

Upvotes: 0

Prashant Pimpale
Prashant Pimpale

Reputation: 10697

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

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

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;

Upvotes: 3

dfundako
dfundako

Reputation: 8314

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

Upvotes: 4

Related Questions