An example to show how to make Tree structure data table in SQL server

When you want to create a tree type datetable in database, it is very convenient to add a Level field which indicate the level of current item.

CREATE   TABLE   [ dbo ] . [ Categories ] (
     [ CategoryID ]   [ nvarchar ] ( 50 ),
     [ ParentCategoryID ]   [ nvarchar ] ( 50 ),
     [ CategoryName ]   [ nvarchar ] ( 50 ),
     [ PopularRate ]   [ int ] ,
     [ Level ]   [ nvarchar ] ( 10 )  NULL ) 

 You can create a SP to insert Category Item. 

 

if   exists  ( select   *   from  dbo.sysobjects  WHERE  id  =   object_id (N ' [dbo].[CreatNewCategory] ' )  AND   OBJECTPROPERTY (id, N ' IsProcedure ' )  =   1 )
drop   procedure   [ dbo ] . [ CreatNewCategory ]
GO
SET  QUOTED_IDENTIFIER  ON  
GO
SET  ANSI_NULLS  ON  
GO
  
CREATE    PROCEDURE   [ dbo ] . [ CreatNewCategory ]    
(  
     @ParentCategoryID   NVARCHAR ( 50 ),
     @CategoryName   NVARCHAR ( 50 )
)  
AS   
BEGIN   
  
/**/ /***************************************  
* Standard generic declared variables  
***************************************/   
DECLARE   
     @Msg    VARCHAR ( 1000 ),    --  Custom Error Message Text  
     @Error    INT ,            --  For capturing @@ERROR immediately after statements  
     @Level   NVARCHAR ( 10 ),
     @ParentLevel   NVARCHAR ( 10 )

SELECT   
     @Error   =   0 ,   
     @Msg   =   ''   
   
/**/ /***************************************  
*  Procedure specific declared variables  
***************************************/   
DECLARE   
     @TransactionExists   bit
/**/ /***************************************  
* Begin Your SQL code  
****************************************/   
BEGIN  TRY  
  
     IF   @@TRANCOUNT   >   0   
         SET   @TransactionExists   =   1   
     ELSE   
         SET   @TransactionExists   =   0   
   
     IF   @TransactionExists   =   0   
     BEGIN   
         -- Initialise transaction     
         BEGIN   TRANSACTION   
     END   


     IF  dbo.StringISNull( @CategoryName )  =   0
     BEGIN
         SET   @msg = ' [CategoryName] can not be empty! '
         RAISERROR ( @msg , 16 , 1 )
     END
    
     IF  dbo.StringISNull( @ParentCategoryID )  =   0
     BEGIN
         SELECT   @Level   =   MAX ( [ Level ] ) 
         FROM  Categories 
         WHERE  ParentCategoryID  IS   NULL   OR  ParentCategoryID  =   ''

         IF  dbo.StringISNull( @Level )  =   0
         BEGIN
             SET   @Level   =   ' 01 '
         END
         ELSE
         BEGIN
             SET   @Level   =   ' 0 '   +   CAST (( CAST ( SUBSTRING ( @Level , 1 , 2 )  AS   INT )  +   1 )  AS   NVARCHAR )
             SET   @Level   =   RIGHT ( @Level , 2 )
         END
     END
     ELSE
     BEGIN
         SELECT   @Level   =   MAX ( [ Level ] ) 
         FROM  Categories 
         WHERE  ParentCategoryID  =   @ParentCategoryID
        
         IF  dbo.StringISNull( @Level )  =   0
         BEGIN
             SET   @Level   =   ' 01 '
         END
         ELSE
         BEGIN
             SET   @Level   =   ' 0 '   +   CAST (( CAST ( RIGHT ( @Level , 2 )  AS   INT )  +   1 )  AS   NVARCHAR )
             SET   @Level   =   RIGHT ( @Level , 2 )
         END
         SELECT   @ParentLevel = [ Level ]   FROM  Categories
         WHERE  CategoryID  =   @ParentCategoryID
         SET   @Level   =   @ParentLevel   +   @Level
     END

     INSERT  dbo.Categories(CategoryID, ParentCategoryID,
                            CategoryName, PopularRate,  [ Level ] )
                 VALUES ( NEWID (),  @ParentCategoryID ,
                         @CategoryName ,  0 ,  @Level )

     IF   @TransactionExists   =   0    
         COMMIT   TRANSACTION    
     RETURN  ( 0 )  
  
END  TRY  
BEGIN  CATCH  
     IF   @TransactionExists   =   0   
         ROLLBACK   TRANSACTION

     IF   @msg = ''
         SET   @msg = error_message()

     RAISERROR ( @msg , 16 , 1 )

     IF   @Error   =   0   
         SET   @Error   =   1   
     RETURN  ( @Error )  
END  CATCH  
END   

GO
GRANT   EXEC   on   [ dbo ] . [ CreatNewCategory ]   to   PUBLIC
GO

 

After you inserting the data, you can only use one sentence to get a tree level data set.

 

SELECT   *   FROM  Categories  ORDER   BY   [ level ]