CODEWITHSUNDEEP
sqlsql-server-2008t-sqlwhile-looptemp-tables
Sagnik Mukherjee
Sagnik Mukherjee

Reputation: 163

Remove while loop in T-SQL statement

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

Upvotes: 0

Views: 622

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93754

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

Upvotes: 1

Related Questions