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!