SQL Server Performance

Count Children

Discussion in 'Contribute Your SQL Server Scripts' started by yew_tec, Oct 16, 2004.

  1. yew_tec New Member

    Create Proc ProcRec
    AS
    Begin
    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

    End

    yew_tec, Oct 16, 2004
    #2

Share This Page