facebook  linkedin  Twitter  skype  Rss googlePlus

Recursive SQL User Defined Functions

Sep 25 2003
5964

Being the developer for this directory, I had a need to perform several recursive methods. Any time you are creating a tree like data structure like directories, org charts, etc. You will need to perform some kind of recursion. In this example, I wanted to be able to provide a count of all descendents sites under a particular category as you see on WWWCoder.com when navigating the site. Each category contains a count of descendent sites of all categories under that specific category.

In the past there were several ways to perform this, one simple method was to create a field in the categories table that would get incremented each time a site was added. This was accomplished using a recursive method in the ASP.Net code that would create a new connection to the database each time a new record was added. It did eliminate the need to call a recursive function for each time a person requested a page to display the category navigation. Basically the method performed would accept a category id and a string value to increment or decrement the count contained in the site count field:

Public Sub UpdateParentCount(ByVal ParentID As Integer, ByVal AddSubtract As String)
 Dim SQLQ As String = "UPDATE Categories Set SiteCount = SiteCount " & AddSubtract & " 1"
 If AddSubtract = "+" Then 'add a new icon.
  SQLQ = SQLQ & ", DateSiteAdded = '" & Date.Today & "'"
 End If
 SQLQ = SQLQ & " WHERE CategoryID=" & ParentID
 Dim secondConnection As New SqlConnection(GetDBConnectionString)
 Dim secondCommand As New SqlCommand(SQLQ, secondConnection)
 secondCommand.CommandType = CommandType.Text
 secondConnection.Open()
 secondCommand.ExecuteNonQuery()
 secondConnection.Close()
 secondConnection = Nothing
 Dim SQLQ2 As String = "SELECT ParentID FROM Categories Where CategoryID = " & ParentID
 Dim myConnection As New SqlConnection(GetDBConnectionString)
 Dim myCommand As New SqlCommand(SQLQ2, myConnection)
 myConnection.Open()
 Dim result As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
 If result.Read Then
  UpdateParentCount(result("ParentID"), AddSubtract)
 End If
 myConnection.Close()
 myConnection = Nothing
End Sub
This wasn't too bad of solution, since I was able to eliminate the hits on the database for the count. The problem here is a count can become incorrect if any changes are performed outside of the application.

The solution for me had to meet the following requirements: reduce traffic between the Web server and the database server, and make sure an accurate count is always available in the database regardless of what modifies the records that it contains. In order to accomplish this, the count method was moved out of the ASP.Net code and into the database. SQL Server 2000 supports User Defined Functions that can be called from a stored procedure, in addition, the function can be recursive up to 32 levels. Since I can't see a need to go beyond 32 levels deep of categories, I opted to use the functions for creating the count. Here is an example of using a function from within a stored procedure:

ALTER procedure GetCategories
 
@ParentID   int
 
as
 
BEGIN
SELECT
    CategoryID, CategoryName, Path, SiteCount, DateSiteAdded,
    ParentID, SortColumn, dbo.CountChildren(CategoryID, 0)
    As CulCount
FROM
    Categories 
WHERE
    ParentID = @ParentID
ORDER BY
    SortColumn, CategoryName
END


You'll notice in the SQL stored procedure's select statement there is a call to a function called CountChildren, in this function we pass the category id of the current category and the current cumulative count of the sites within the category.
ALTER FUNCTION dbo.CountChildren
(@id int, @cChildren int)
RETURNS bigint
AS
BEGIN
 
IF EXISTS (SELECT    
    Sites.SiteCatID
FROM        
    dbo.Categories
INNER JOIN
    dbo.Sites
ON
    dbo.Categories.CategoryID = dbo.Sites.SiteCatID
WHERE
    dbo.Categories.ParentID = @id OR dbo.Sites.SiteCatID = @id)
BEGIN
   SET @cChildren = @cChildren + (
     SELECT
        Count(SiteCatID)
        FROM
            Sites
        WHERE
            SiteCatID = @id AND SiteActive = 1)
  SELECT
              @cChildren = dbo.CountChildren(CategoryID, @cChildren)
            FROM
              Categories
            WHERE
              ParentID = @id
END
  RETURN @cChildren
END

As you can see the function calls itself just as a recursive function in VB.Net would do, each time incrementing the cumulative count of all descendents of a particular category. In the end we have all the information generated on the SQL machine, and then it returns what we need without having to call a recursive method in the ASP.Net page and generate all the additional database calls over the network.

-P

About the Author, Patrick Santry

Patrick Santry, has two decades of experience in enabling businesses to take advantage of the digital landscape. A well rounded experience in technology, and business is what sets me apart from the rest of the pack. When it comes to an overall digital strategy my experience is impressive.

BS in Computer Information Systems. Four time recipient of the Microsoft MVP Award, and author of several books and magazine articles on digital technologies.


blog comments powered by Disqus

 

Thank you for visiting our site, before you leave, please visit some of the areas or information you may have missed.

Popular Articles