Hi

If you have a Self Referencing Table one of the major tasks is to get the parents of the given category. This will be required when you want to show the bread-crumb, and many other things.

For this reason its best to create a self referencing function which will give back a table having all the parent values. This function then can be used to get the entire parent category and work with it.

For our example we are using a Self referencing NLevelCategry table which is like this

NLevelcategoryId  bigint
NLevelcategory    varchar(50)
ParentID          bigint 

Here the parent Id is the Id of the parent category for the record. For root category it will contain "0".

Now to get the list of all the parents for a given category we create a function to get a list of the entire parent category.

CREATE FUNCTION dbo.GetParents(@IncludeParent bit, @NLevelcategoryId int)

RETURNS @retFindReports TABLE (NLevelcategoryId int, NLevelcategory varchar(50), ParentID int)

AS 

BEGIN

      IF (@IncludeParent=1)

      BEGIN

            INSERT INTO @retFindReports

            SELECT NLevelcategoryId, NLevelcategory,ParentID

             FROM NLevelCategory WHERE NLevelcategoryId=@NLevelcategoryId

      END

IF (@NLEVELCATEGORYID=0)

      BEGIN

            RETURN

      END

      DECLARE @Report_ID int, @Report_NLevelcategory varchar(50), @Report_ParentID int

      DECLARE RetrieveReports CURSOR STATIC LOCAL FOR

      SELECT  NLevelcategoryId, NLevelcategory, ParentID

      FROM NLevelCategory WHERE NLevelcategoryId=@NLevelcategoryId

      OPEN RetrieveReports

      FETCH NEXT FROM RetrieveReports

      INTO @Report_ID, @Report_NLevelcategory, @Report_ParentID

      WHILE (@@FETCH_STATUS = 0)

      BEGIN

            INSERT INTO @retFindReports

            SELECT * FROM dbo.GetParents(1, @Report_ParentID)

            FETCH NEXT FROM RetrieveReports

            INTO @Report_ID, @Report_NLevelcategory, @Report_ParentID

      END

      CLOSE RetrieveReports

      DEALLOCATE RetrieveReports

      RETURN

END

The Function has 2 parameters IncludeParent (whether to include the current record) and NLevelcategoryId (the ID of the category to get records). The function calls itself again and again to get all the parent records.

Now we can use this function to work with the entire parent category for a given category.

Hope this helps
Thanks
Vikram