• thanks alot LutzM the problem is solved . I have one another problem in here how can i parameterized this cte incase i want to pass locationid at any level.

    if i pass ID =1 it shall pass take all the locations under 1, if 2 it shall look for locationid 3 and 4.

    means if i pass parent it shall look for all it's childs entry location in project page.

    do i need to create another cte,or is it possible to update this.

    CREATE TABLE [dbo].[LocationTable](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [LocationName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [ParentID] [int] NULL,

    [LevelID] [int] NULL)

    insert into [LocationTable](LocationName,ParentId,Levelid) values('A',NULL,1)

    insert into [LocationTable](LocationName,ParentId,Levelid) values('B',1,2)

    insert into [LocationTable](LocationName,ParentId,Levelid) values('C',2,3)

    insert into [LocationTable](LocationName,ParentId,Levelid) values('D',2,3)

    insert into [LocationTable](LocationName,ParentId,Levelid) values('E',1,2)

    insert into [LocationTable](LocationName,ParentId,Levelid) values('F',5,3)

    go

    alter table project

    add locationid int

    go

    update project set locationid=3 where id=1

    update project set locationid=4 where id=3