How To Get All children and itself from Hierarchical data with CTE In SQL Server 2005 using stored procedure

I have many similar structure tables like this:

CREATE TABLE [dbo].[tbl_Hierarchy](
[ID] [int]  NOT NULL,
[ParentID] [int] NOT NULL,
[Text] [nvarchar](100)  NOT NULL,
--other field irrelevant to my question
)

INSERT INTO dbo.tbl_Hierarchy VALUES(1,0,'parent1')
INSERT INTO dbo.tbl_Hierarchy VALUES(2,0,'parent2')
INSERT INTO tbl_Hierarchy VALUES(3,1,'child1')
INSERT INTO tbl_Hierarchy VALUES(4,3,'grandchild1')
INSERT INTO  tbl_Hierarchy VALUES(5,2,'child2')

Can you help me writing such as a stored procedure including two parameters with table name and ID ?

  • PHP MsSQL PDO or API
  • how to import data from other database in SQL Server 2005
  • how to use sql server time data type in .net application?
  • How to check if a database exists in SQL Server?
  • Server Explorer unavailable (VS2008Pro)
  • Find max value and show corresponding value from different field in SQL server
  • For example, when executing

    EXEC usp_getChildbyID  tbl_Hierarchy, 1
    

    the result set should be:

    ID  Text        Level
    1   parent1      1
    3   child1       2
    4   grandchild1  3
    

    Thanks a lot in advance.

  • Returning all children with a recursive select
  • Bizarre performance issue: Common Table Expressions in inline User-Defined Function
  • CTE Recursion to get tree hierarchy
  • How to use a CTE statement in a table-valued function in SQL Server
  • Data changes in a CTE after SELECT query
  • SQL CTE Recursion: Returning Parent Records
  • One Solution collect form web for “How To Get All children and itself from Hierarchical data with CTE In SQL Server 2005 using stored procedure”

    This recursive CTE should do the trick.

    WITH RecursiveCte AS
    (
        SELECT 1 as Level, H1.Id, H1.ParentId, H1.Text FROM tbl_Hierarchy H1
        WHERE id = @Id
        UNION ALL
        SELECT RCTE.level + 1 as Level, H2.Id, H2.ParentId, H2.text FROM tbl_Hierarchy H2
        INNER JOIN RecursiveCte RCTE ON H2.ParentId = RCTE.Id
    )
    SELECT Id, Text, Level FROM RecursiveCte
    

    If you really want it with a dynamic table in a procedure this could be a solution

    CREATE PROCEDURE usp_getChildbyID
        @TableName nvarchar(max),
        @Id int
    AS
    BEGIN
    
        DECLARE @SQL AS nvarchar(max)
        SET @SQL = 
        'WITH RecursiveCte AS
        (
            SELECT 1 as Level, H1.Id, H1.ParentId, H1.Text FROM ' + @TableName + ' H1
            WHERE id = ' + CAST(@Id as Nvarchar(max)) + '
            UNION ALL
            SELECT RCTE.level + 1 as Level, H2.Id, H2.ParentId, H2.text FROM ' + @TableName + ' H2
            INNER JOIN RecursiveCte RCTE ON H2.ParentId = RCTE.Id
        )
        select Id, Text, Level from RecursiveCte'
    
        EXEC sp_executesql @SQL;
    END
    

    Edit:

    Sql fiddle example: http://sqlfiddle.com/#!3/d498b/22

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.