Saturday, January 4, 2014

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.

No comments:

Post a Comment