Remove while loop in T-SQL statement

Question

There were some while loops in a procedure and I want to remove them / replace with temp tables or any other ( not cursor) .

Below I have created a dummy syntax, I have to populate the TEMP_TABLE table based on some calculations. Please share any better option to do the same:

SET @J = 0
SET @DIFFERENCE = 10
SET @INCREMENT = 2

WHILE (@J < @DIFFERENCE) 
BEGIN   
    SET @TO_DATE = (SELECT DATEADD(D, @INCREMENT, @TO_DATE))   

    IF (@TO_DATE <= GETDATE())
       INSERT INTO TEMP_TABLE 
       VALUES(@J, @TO_DATE)   

    SET @J = @J + @INCREMENT  
END

There are many examples on SO of using tally tables to populate date table? Did you try to search anything? The clue is to generate number range and use simple INSERT INTO ... SELECT ... FROM (tally) ... WHERE (loop condition) — Lukasz Szozda
– Lukasz Szozda, Commented Dec 24, 2015 at 7:29

Pரதீப் · Accepted Answer · 2015-12-24 07:41:30Z

1

Here is one way using Tally table

;WITH e1(n) AS
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 10
e2(n) AS (SELECT 1 FROM e1 CROSS JOIN e1 AS b), -- 10*10
e3(n) AS (SELECT 2 FROM e1 CROSS JOIN e2), -- 10*100
e4(n)  as ( SELECT n = ROW_NUMBER() OVER (ORDER BY n) FROM e3)
SELECT n,
       Dateadd(D, n, @TO_DATE)
FROM   e4
WHERE  N <= @DIFFERENCE
       AND N%@INCREMENT = 0

For more info on Generate a set or sequence without loops

answered Dec 24, 2015 at 7:41

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

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

Collectives™ on Stack Overflow

Remove while loop in T-SQL statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related