Hi

Many a time we need a table with hierarchy data. We cannot define the level of the hierarchy at the beginning but is supposed to decide by the data. At these points its best to use a table which stores the hierarchy and display data from that table. One of the better hierarchy is example is the product catalog for an online store.

But SQL Server is a relational database, not a hierarchical database. So, we have to store the data in normalized, relational tables, but come up with programming techniques to process this data in a hierarchical manner. We do not have any built in support in SQL server 2000 for this.

To this we first create a  Category table

CREATE TABLE Category
(

      CatID       int   PRIMARY KEY,

      CatName           varchar(30),

      ParentCatId int   FOREIGN KEY REFERENCES Category(CatID)

)

GO

Here the CatID is the primary Key and ParentCatId is referencing the CatID column. This means that ParentCatID can have only those values which are there in the CatID column. This type of table is called Self Referencing table as one field of the table is referencing another field in the same table.

[Note To improve any query performance we can add a non clustered Index in the ParentCatId field.]

Lets enter some data in the table.

Insert into Category values(4, 'Computers' , Null)

Insert into Category values(5, 'Humour' , Null)

Insert into Category values(6, 'Health & Living' , Null)

Insert into Category values(7, 'Programming' , 4)

Insert into Category values(8, 'Software for Dummies' , 4)

Insert into Category values(9, 'Graphics & Multimedia' , 4)

Insert into Category values(10, 'Microsoft' , 7)

Insert into Category values(11, 'Oracle' , 7)

Insert into Category values(12, 'Java / Sun' , 7)

Notice that the ParentCatId of first three values are Null. These are the main category (Master category). These are not under any category. Now let us create a Procedure that will run down each one of them and display the value in a indented tree structure. The procedure should take a parameter (the root category) to start displaying the value.

CREATE PROC dbo.ShowHierarchy

(

      @Root int

)

AS

BEGIN

      SET NOCOUNT ON

      DECLARE @CatID int, @CatName varchar(30)

      SET @CatName = (SELECT CatName FROM dbo.Cat WHERE CatID = @Root)

      PRINT REPLICATE('-', @@NESTLEVEL * 4) + @CatName

      SET @CatID = (SELECT MIN(CatID) FROM dbo.Cat WHERE MgrID = @Root)

      WHILE @CatID IS NOT NULL

      BEGIN

            EXEC ShowHierarchy @CatID

            SET @CatID = (SELECT MIN(CatID) FROM dbo.Cat WHERE MgrID = @Root AND CatID > @CatID)

      END

END

GO

When we create this procedure we get a message

“Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'ShowHierarchy'. The stored procedure will still be created.”

Don’t worry about this. The stored procedure is created for us. This is because the SQl server cannot find the self referenced procedure.

[Note: The maximum number of hierarchy that the SQL server can handle is 32]

When we run the procedure we get the following result.

Exec ShowHierarchy 4 We get the following result.

----Computers

--------Programming

------------Microsoft

------------Oracle

------------Java / Sun

--------Software for Dummies

--------Graphics & Multimedia

Hope this helps
Thanks
Vikram