Recursive Function example in SQL Server

In one of my project, I had to process some information by retrieving some data in an SQL Server database which were somewhat like as follows:

There was a table ItemNames

And, there was another table named ItemNameMaps

Note that, the ItemNames table contains the name of the items and a flag IsCurrent to denote which names are currently active, and, the ItemNameMaps table contains a mapping between the OldIds and the NewIds.

When an item name is modified from the front-end, the name is not actually modified in the database. Rather, the following series of actions take place:

• A new entry is stored in the ItemNames table with the modified name, along with setting IsActive = true.

• A new entry is stored in the ItemNameMaps table with the newly inserted Id of the entry in ItemNames table (NewId) and the Id of the entry in ItemNames table containing the Existing name of the iteam (OldId).

• The IsActive is set to false for the row in ItemNames table containing the old Name for the same item.

So, given the above data structure, the requirement was to retrieve all the old names of a given item's Id. 

In the ItemMaps table, as you can see, for a given Item's Current Id (Say, 8),  it is possible to know what were the Id's of the past name of the items. That is:

6-->8 (Item Id 6 was renamed to Item Id 8, A2 was renamed to A3)

4-->6 (Item Id 4 was renamed to Item Id 6, A1 was renamed to A2)

1-->4 (Item Id 1 was renamed to Item Id 4, A   was renamed to A1)

So, for Item Name A3 (Id=8), we would like to retrieve the following Information:

Guess what, I retrieved the data using a recursive User Defined Function (UDF) in SQL Server.

The Recursive function

Before diving into the Recursive Function, let me derive the base logic first. For a given Item's Id (Say, 8), the data is to be retrieved using the following logic:

RetrieveOldNames(ItemId)
Begin
    OldId = Get OldId for the given ItemId From ItemNameMaps 

    Select Name (OldId) From ItemNames + Select Name FROM RetrieveOldNames(OldId)
End

Fore example, for the given Item Id = 8, the logic will execute as follows:

1. It will retrieve the OldId = 6 for the given Item Id 8

2. It will retrieve the Name for OldId 6 (A2)

3. It will call the recursive function for the OldId 6 (Go back to step 1).

Following is the actual UDF, which worked like a charm:

CREATE FUNCTION [dbo].[GetOldNames] (@Id int)  
RETURNS @OldNames TABLE (Name varchar(50))
 AS  
BEGIN 
    DECLARE @OldId bigint

    SET @OldId = (SELECT OldId FROM ItemNameMaps WHERE [NewId] = @Id)
    IF @OldId IS NULL or @OldId = 0 return 
    
    INSERT INTO @OldNames(Name)
        SELECT (SELECT Name FROM ItemNames WHERE Id = @OldId)
        FROM ItemNames
        UNION
        SELECT Name FROM GetOldNames(@OldId)
     RETURN
END

I executed the function using the following TSQL:

SELECT * from GetOldNames(8)

And, was happy to see the following result: