Examples of infinite loops in SQL Server

Here are some examples of simple infinite loops in SQL Server. When you write a loop, you want to make sure that it never runs infinitely. Examples of infinite loops provided here can be used for testing purposes in a testing environment, and to learn to recognize and to avoid them.

The WHILE loop continues until it is interrupted by a BREAK statement, or until the specified condition is no longer true. Here are two examples of infinite WHILE loops:

Example 1:

DECLARE @value int
SET @value = 1
WHILE (@value < 5) BEGIN PRINT 'This is an infinite loop!' WAITFOR DELAY '00:00:01'; END

Example 2:

WHILE 1=1
BEGIN
SELECT 'This is an infinite loop!'
END

To avoid the SQL statement to run in an infinite WHILE loop, use BREAK where needed:

WHILE 1 = 1
BEGIN
-- Add some SQL statements that you need
SELECT 'This is not an infinite loop any more!'
-- use BREAK to stop execution
BREAK;
END

The next example will run in a loop for the number of seconds you specify (in this example 30 seconds), and then it will end:

DECLARE @start_time datetime2(0) = GETDATE();
WHILE (GETDATE() < DATEADD(SECOND, 30, @start_time))
BEGIN
WAITFOR DELAY '00:00:01';
END

Here is an example of an infinite loop using CTE. The server-wide default for maximum recursion is 100. When 0 is specified, using option (MAXRECURSION 0), no limit is applied, and the CTE will run in an infinite loop:

WITH infinite_cte AS
(SELECT 1 as x
UNION all
SELECT x + 1
FROM infinite_cte)
SELECT x
FROM infinite_cte OPTION (MAXRECURSION 0)

If we do not set the option (MAXRECURSION 0), the default maximum recursion 100 will been exhausted before statement completion:

WITH test_cte AS
(SELECT 1 as x
UNION all
SELECT x + 1
FROM test_cte)
SELECT x
FROM test_cte

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">