Recursion vs. Loops in VBA and SQL

Recursion, when I was first taught about it in our class rooms our professor mentioned, this is one topic which every student ask to repeat once again.

Though after a while in the business I am learning actually it’s not that bad too. But it’s just a concept which has to be used wisely. In the past the context I have used recursion is for file/directory searches and listing. But I was also taught to us in the context of factorial and mathematical concepts which could be achieved via loops.

Thus I am embarking in this post to figure out which is a better one to choose:

With recursion, though our mathematical models can be represented well in comparison to loops programmatically. The concept of recursion has some underlying concerns which also need to be taken into account while designing the programs.

The following concerns are sourced from MSDN (for further details please refer to the detailed MSDN documentation):

1. Limiting condition: This is the most important part of recursion and upon which many times my programs have crashed up. Design the recursion ending condition well for all scenarios of the input, if not you might end up into stack overflow error.

2. Memory Usage/Performance: This is one of the most important considerations to take into account while writing code for recursion. As the function/procedure calls upon itself each time the copy of local variable for each instance of execution is created on stack and the overhead of argument passing has to be taken.

3. Debug: Painful to debug in recursive codes.

So rather stick with Looping?? Well it’s an answer you as developer have to decide which route to take; personally I have used recursion for scenarios of file/directory listing arenas and mostly stick to the simple forms of looping for any other tasks of computations. But your comments are valuable for me in this space .. !!!

Below are the codes to illustrate the use of recursion in VBA and in SQL (too but only limited to 32 levels)

VBA:

Option Explicit

Public Function recursive_fact(n As Integer) As Integer
    If n < 1 Then
        recursive_fact = 1
        Exit Function
    End If
    recursive_fact = n * recursive_fact(n - 1)
End Function

Public Function looping_fact(n As Integer) As Integer
    Dim jCount As Integer
    looping_fact = 1
    For jCount = n To 1 Step -1
        looping_fact = looping_fact * jCount
    Next jCount
End Function

Sub test_functions()
    Dim iCount As Integer
    Dim jCount As Integer
    iCount = 5
    jCount = 5
    iCount = recursive_fact(iCount)
    jCount = looping_fact(jCount)
    Debug.Print "Factorial of iCount: " & iCount
    Debug.Print "Factorial of jCount: " & jCount
End Sub
'--Output--
'Factorial of iCount: 120
'Factorial of jCount: 120

SQL:

--Create a sample table
CREATE TABLE employee(
    id          INTEGER NOT NULL PRIMARY KEY,
    first_name  VARCHAR(10),
    last_name   VARCHAR(10),
    salary      DECIMAL(10,2),
    start_Date  DATETIME,
    region      VARCHAR(10),
    city        VARCHAR(20),
    managerid   INTEGER
 );
--Insert some records into it
INSERT INTO employee VALUES (1, 'Jason' ,  'Martin', 5890,'2005-03-22','North','Vancouver',3);
GO
INSERT INTO employee VALUES (2, 'Alison',  'Mathews',4789,'2003-07-21','South','Utown',4);
GO
INSERT INTO employee VALUES (3, 'James' ,  'Smith',  6678,'2001-12-01','North','Paris',5);
GO
INSERT INTO employee VALUES (4, 'Celia' ,  'Rice',   5567,'2006-03-03','South','London',6);
GO
INSERT INTO employee VALUES (5, 'Robert',  'Black',  4467,'2004-07-02','East','Newton',7);
GO
INSERT INTO employee VALUES (6, 'Linda' ,  'Green' , 6456,'2002-05-19','East','Calgary',8);
GO
INSERT INTO employee VALUES (7, 'David' ,  'Larry',  5345,'2008-03-18','West','New York',9);
GO
INSERT INTO employee VALUES (8, 'James' ,  'Cat',    4234,'2007-07-17','West','Regina',9);
GO
INSERT INTO employee VALUES (9, 'Joan'  ,  'Act',    6123,'2001-04-16','North','Toronto',10);
GO
--Verify the data
SELECT * FROM employee;
GO  
 
--Create the procedure
CREATE PROC usp_FindBoss(
   @EmployeeID int
 )
 AS
 DECLARE @ReportsTo int
 SELECT
   @ReportsTo = managerid
 FROM
     Employee
 WHERE
     Id = @EmployeeID
 IF @ReportsTo IS NOT NULL AND @@NESTLEVEL <= 32
 BEGIN
   SELECT
     @EmployeeID AS Employee,
     @ReportsTo  AS Manager
   EXEC usp_FindBoss
     @ReportsTo
 END
 GO
 
--Execute the procedure
SELECT * FROM employee
EXEC usp_FindBoss 2

Note: For SQL Stored Procedures CAN call Stored Procedures & Functions(Both), but Functions CAN call Functions (Only).

Refrences:
Link1 (MSDN Recursion): http://msdn.microsoft.com/en-us/library/81tad23s%28v=vs.80%29.aspx
Link2: http://stackoverflow.com/questions/660337/recursion-vs-loops