How to repeatedly execute a statement on a conditional logic.

Problem: How to execute a query repeatedly based on a conditional logic.

Solution: WHILE

Sets a condition for the repeated execution of an SQL statement or statement block. The statements are executed repeatedly as long as the specified condition is true. The execution of statements in the WHILE loop can be controlled from inside the loop with the BREAK and CONTINUE keywords.

While loop can be used to generate incremental numbers, execute command for n number of times.

Example:

In this article I am going to show you how we can use a while loop to create a dates table based on loop insertion.

DECLARE @rows INT = 10,

                @i INT = 0, --starting increment

                @j INT = 100 -- maximum value of loop

CREATE TABLE #temp (

                id INT,

                incrementaldate DATETIME

                )

WHILE (@i <= @j)

BEGIN

                INSERT INTO #temp (

                                id,

                                incrementaldate

                                )

                SELECT @i,

                                CAST(DATEADD(dd, @i, getdate()) AS DATE)

                SET @i = @i + 1;--increments

END

SELECT *

FROM #temp

OUTPUT: