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
