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