Three ways to organize loop in SQL.

The exercise task is to come up with Stored Procedure that will print whole numbers starting from 0.

Here is the easiest one based on a While loop:

USE TEMPDB

GO

IF EXISTS (

SELECT TOP 1 1

FROM sys.procedures

WHERE name = 'usp_LoopTest'

)

DROP PROC usp_LoopTest

GO

CREATE PROC usp_LoopTest

@Param INT

as

DECLARE @i INT = 0;

WHILE @i <= @Param

BEGIN

  PRINT CAST(@i AS VARCHAR)

  SET @i += 1;

END

GO

EXEC usp_LoopTest 10

GO

DROP PROC usp_LoopTest

GO

The second shot is more complicated using operator GOTO:

USE TEMPDB

GO

IF EXISTS (

SELECT TOP 1 1

FROM sys.procedures

WHERE name = 'usp_LoopTest'

)

DROP PROC usp_LoopTest

GO

CREATE PROC usp_LoopTest

@Param INT

as

DECLARE @i INT = 0;

NoLoop:

PRINT CAST(@i AS VARCHAR)

SET @i += 1;

IF @i <= @Param GOTO NoLoop

GO

EXEC usp_LoopTest 20

GO

DROP PROC usp_LoopTest

GO

The third method is  not so obvious, it uses recursion:

USE TEMPDB

GO

IF EXISTS (

SELECT TOP 1 1

FROM sys.procedures

WHERE name = 'usp_LoopTest'

)

DROP PROC usp_LoopTest

GO

CREATE PROC usp_LoopTest

@Param INT

as

SET @Param -= 1;

IF @Param >= 0 EXEC usp_LoopTest @Param;

PRINT CAST(@Param + 1 AS VARCHAR)

GO

EXEC usp_LoopTest 30

GO

DROP PROC usp_LoopTest

GO

Only the problem with recursion - it does not go further than 32 levels deep.