Hi

While working with a self referencing table when we need to delete a record, we also need to delete all the records inside that category. Hence the delete should have a cascading effect on the records.

For this reason its best to create a self referencing function which will give back a table having all the child values. This function then can be used to get the entire child category list and delete them with and in statement.

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 the entire child category for a given category we create a function to get a list of the entire list of child category.

CREATE FUNCTION dbo.GetChild(@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 parentid =@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.GetChild(1, @Report_ID)

--          INSERT INTO @retFindReports

--          VALUES(@Report_ID,@Report_NLevelcategory, @Report_ParentID)

            FETCH NEXT FROM RetrieveReports

            INTO @Report_ID, @Report_NLevelcategory, @Report_ParentID

      END

      CLOSE RetrieveReports

      DEALLOCATE RetrieveReports

      RETURN

END

Now we can delete the entire category inside given category with a simple delete statement like this

DELETE FROM NLevelCategory

WHERE     NLevelcategoryId in( select NlevelCategoryid FROM dbo.GetChild(1, @NLevelCategoryId))

This way the entire category list inside the given category gets deleted and you will not have any orphan data in your database.

Hope this helps
Thanks
Vikram