Recursive Call in Stored Procedures

Recursion is a way of allowing a function to call itself. It results to an iteration of process. Let us have the simple example below that computes for the result of an exponent function:

static void Main(string[] args)
{
double result = exponent(3,4);
Console.WriteLine(result);
Console.ReadKey();
}

private static double exponent(double _base, double power)
{
double result= _base;
if (power > 0)
{
result = result * exponent(_base, power - 1);
}
else
{
result = 1;
}

return result;
}

This is the sample recursive call function in C#. Take note that in recursion there should be a way to terminate the recursion through conditions or else it will bring you to an infinite loop.

Now how can we implement this in SQL? It is allowed to call a stored procedure within a stored procedure. Unfortunately Stored procedure does not return values directly. In case of User Defined Function(UDF), it is not allowed to call a function within a function. Recursions should have a return value in order to determine how the loop will terminate.

The trick here is to use Temporary table to communicate with the calling stored procedure. For simplicity, let's use the same example in getting the result of an exponent:

ALTER PROCEDURE [dbo].[RecursiveCallExponent] (
@base float,
@power float
)
AS
BEGIN

IF(@power > 0)
BEGIN
UPDATE #Result SET Result = Result * @Base
SET @power = @power -1
exec RecursiveCallExponent @base, @power
END
END
GO
-------------------------

ALTER PROCEDURE GetExponent (
-- Add the parameters for the stored procedure here
@base float,
@power float
)
AS
BEGIN
CREATE TABLE #Result (Result float)
insert into #Result (Result) values (1 )
exec RecursiveCallExponent @base, @power

SELECT Result FROM #Result
DROP TABLE #Result
END
GO

GetExponent here is the main stored procedure call. Observe that The Temporary table is created in the main stored procedure to be used by the recursive procedure (RecursiveCallExponent). We cannot create it inside the recursive procedure for it will raise "Object exists" error since we are iterating inside it. Now we can run
"EXEC GetExponent(3,4)"
and will give you a result of 81.

I used recursive on my application to get all consultant with a specific skill in the Heirarchy of skillsets. The scenario is this, I have a Hierachical table which represents the Heirarchy of Skills, e.g. Words, Excel and Powerpoint under MSOffice and MSOffice, Explorer and Media Player under Windows. When I choose specific skills like MSOffice, I should be able to get all consultant under MSOffice including those consultant who has special skills for Powerpoint, Excel and Word and even those under it if it still have sub categories.