TomBecker.net

Recursive Functions in SQL 2000

Recently I was asked for a list of all the history associated with a row in a database. In this case when a row in the table was updated, we did an insert with the updates and updated the old row with a pointer to the new row. We needed to do this so we could show history when needed without having to create a history table.

So since I didn't know how many ancestors a row could have, I decided to use recursion. And for those of you who never had a programming class with me know that I really don't like recursion, it confuses me. But it had to be done. So below is the recursive function that can be called to get a list of all ancestors of a given row:

CREATE FUNCTION [dbo].[udf_GetAncestors] 

(@CurrID int)  

RETURNS varchar(50)  AS  
BEGIN 

DECLARE
@ancestorIDs varchar(50),
@NextID INT


SELECT  @NextID  = ItemID  
FROM   Items
WHERE RevisedItemID= @CurrID;

SET @ancestorIDs = Convert(varchar, @CurrID);

IF  @NextID IS NOT NULL BEGIN
  SET @ancestorIDs = '''' + @ancestorIDs + ''', ' + dbo.udf_GetAncestors(@NextID);
END

Return @ancestorIDs 

END

Labels: SQL

posted by Tom Becker at 6/26/2009