Recursion using Functions and Stored Procedures in SQL Server

When considering recursion in SQL, developers typically think of Common Table Expressions (CTEs) to solve hierarchical models. However, SQL Server also allows for recursive functions and stored procedures, a feature not in Microsoft’s documentation or the literature I have encountered.

This approach to recursion is less known, and rightly so, as there are usually more efficient solutions to data problems than using recursive functions or procedures. Nevertheless, I am constantly exploring unconventional methods of approaching SQL and discovering methods for writing SQL queries that I can share with the SQL community.

First, we will develop a recursive function that accepts an input string and recursively calls itself until it returns the leftmost character. Of course, the example isn’t practical, as this could quickly be done with the LEFT function, but it demonstrates the use of creating recursive elements in SQL. Also, I have yet to identify a practical application for creating a recursive function or stored procedure, so consider these examples for amusement only!

CREATE OR ALTER FUNCTION fnStringReduction (@vInputString VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
    DECLARE @vResult VARCHAR(100);

    IF LEN(@vInputString) = 1
    BEGIN
        SET @vResult = @vInputString;
    END
    ELSE
    BEGIN
        DECLARE @vMyString1 VARCHAR(100);

        -- Reduce the string by removing the last character
        SET @vMyString1 = LEFT(@vInputString, LEN(@vInputString) - 1);

        -- Recursively call the function with the reduced string
        SET @vResult = dbo.fnStringReduction(@vMyString1);
    END
    
    RETURN @vResult;
END;
GO


--Example usage
SELECT dbo.fnStringReduction('ABCDEFG') AS ReducedString;
GO

Next, we will create a stored procedure that displays a table featuring the leftmost character of a given string.

CREATE OR ALTER PROCEDURE spStringReduction
    @vInputString VARCHAR(100),
    @vResult VARCHAR(100) OUTPUT
AS
BEGIN
    IF LEN(@vInputString) = 1
    BEGIN
        SET @vResult = @vInputString;
    END
    ELSE
    BEGIN
        DECLARE @vMyString1 VARCHAR(100);
        -- Reduce the string by removing the last character
        SET @vMyString1 = LEFT(@vInputString, LEN(@vInputString) - 1);
        EXEC spStringReduction @vMyString1, @vResult OUTPUT;
    END
END;
GO


DECLARE @Result VARCHAR(100);
EXEC spStringReduction @vInputString = 'ABCDEFG', @vResult = @Result OUTPUT;
SELECT @Result AS ReducedString;

Happy coding!