Thanks a lot. However, the requirement got changed and had to modify the procedure. This is the proc

alter Procedure GetCalculationForTestData

(

@taskId int,

@fatherId int,

@taskLevel int

)

as begin

Create table #TestFinal

(

id_task int primary key,

id_fathertask int,

level_task int,

duracion_task int,

percent_task int null,

name_task varchar(24),

Calculation int,

Calculation1 int

);

Insert #TestFinal (id_task, id_fathertask, level_task, duracion_task, percent_task, name_task)

select id_task, id_fathertask, level_task, duracion_task, percent_task, name_task

from [Reportes].[dbo].[FunctionTST1]

While(@fatherId <> 0)

Begin

While(@taskLevel <> 0)

Begin

Create table #TestData

(

id_task int primary key,

id_fathertask int,

level_task int,

duracion_task int,

percent_task int null,

name_task varchar(24),

Calculation int,

Calculation1 int

);

Insert #TestData (id_task, id_fathertask, level_task, duracion_task, percent_task, name_task)

select id_task, id_fathertask, level_task, duracion_task, percent_task, name_task

from [Reportes].[dbo].[FunctionTST1]

where level_task = @taskLevel and id_fathertask = @fatherId

declare @Calc int = 0

declare @Calc1 int = 0

declare @temp int = 0

;with cteOrdered as

(

select * from #TestData

)

update cteOrdered

set @Calc = Calculation = (percent_task * duracion_task)

from cteOrdered WITH (TABLOCKX)

OPTION (MAXDOP 1)

update cteOrdered

set @Calc1 = Calculation1 = sum(Calculation)/sum(duracion_task)

from cteOrdered WITH (TABLOCKX)

OPTION (MAXDOP 1)

update #TestFinal set percent_task = @Calc1 where @taskId = @fatherId

drop table #TestData

set @temp = @taskLevel -1

execute GetCalculationForTestData1 @taskId,@fatherId,@temp

End

set @temp=0

set @temp = @fatherId - 1

execute GetCalculationForTestData1 @taskId,@temp,@taskLevel

End

select * from #TestFinal

End

But I am having one error on line 62. Invalid object name 'cteOrdered'. Can anyone help me to solve this?