Infinite loop in SQL?

There is some way to implement a infinite loop in SQL?? I was thinking on some li开发者_开发知识库ke a select inside another one, recursively... (Maybe im talking foolishness)

You can do a recursive infinite loop with a CTE:

;with rec as
        (
        select  1 as n
        union all
        select  n + 1
        from    rec
        )
select  n
from    rec

By default, SQL Server would stop at 100; you can make it loop forever with:

option  (maxrecursion 0)

WHILE 1=1
BEGIN
SELECT 'This will go forever'
END

This should work in oracle

select * from (select level x from dual connect by  level >=0)

the outer select is needed to avoid some optimizations which happen with some combinations of client and database.

Not completely sure if the level >= 0 part works as intended, since normally you would use this only with something like level <= 50 to get a fixed number of rows.

I can't imagine why you would want to do this, but it's going to depend on your implementation's (SQL Server, Oracle, MySQL...) supported loop constructs.

For example, in SQL Server, you would write an infinite loop the way you would in any imperative language. Roughly:

DECLARE @x INT = 0
WHILE (@x = 0)
BEGIN
  PRINT @x
END

DECLARE @number INT = 3

WHILE (@number = 3)
BEGIN
    PRINT @number
END

You can't create an infinite loop with a declaritive language like SQL because the compiler's won't let you. You will get error messages and/or it just won't run.

You need to do something like what JNK suggested, using SQL to write something sequential.