Dynamic sql error on a proc

See more:

SQL

Hello,
using sql server 2008, having a problem with a stored proc that worked fine when it was not in dynamic sql form.
Error message is Msg 156, Level 15, State 1, Line 99
Incorrect syntax near the keyword 'All'.
Below the code

USE [F2B_VOM_TST]
GO
/****** Object:  StoredProcedure [p_data].[P_Moa_GetAumNavBenchOnPtsId]    Script Date: 11/10/2013 19:40:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [p_data].[P_Moa_GetAumNavBenchOnPtsId]
@DateDebut dateTime,@DateFin datetime,@Part integer,@Devise varchar(10),@PtfId integer,@Frequence varchar(1),@TypePerf integer=0

WITH EXEC AS CALLER
AS

Set Nocount On


Declare @DefaultQuery AS NVarchar(max)
--Declare @VlAbsoluteQuery AS NVarchar(max)
--Declare @VlRelativeQuery AS NVarchar(max)
--Declare @AumQuery AS NVarchar(max)

Declare @Instruction0 AS NVarchar(max)
Declare @Instruction1 AS NVarchar(max)
Declare @Instruction2 AS NVarchar(max)
Declare @Instruction3 AS NVarchar(max)
Declare @Instruction4 AS NVarchar(max)
Declare @Instruction5 AS NVarchar(max)
Declare @Instruction6 AS NVarchar(max)
Declare @Instruction7 AS NVarchar(max) 
Declare @Instruction8 AS NVarchar(max) 
Declare @Instruction9 AS NVarchar(max) 
Declare @Instruction10 AS NVarchar(max) 
Declare @Instruction11 AS NVarchar(max) 
Declare @Instruction12 AS NVarchar(max) 
Declare @Instruction13 AS NVarchar(max) 
Declare @Instruction14 AS NVarchar(max) 
Declare @Instruction15 AS NVarchar(max) 
Declare @Instruction16 AS NVarchar(max) 
Declare @Instruction17 AS NVarchar(max) 
Declare @Instruction18 AS NVarchar(max)
Declare @Instruction19 AS NVarchar(max)
Declare @Instruction20 AS NVarchar(max)
Declare @Instruction21 AS NVarchar(max)





------ 0,1,2,3 Begin
--Set @Instruction0 = 


create table #TEMP_VL1
    ( [PtsId] int,
      [Date]  datetime
    )

declare @FinAnneePrecedente datetime 

select @FinAnneePrecedente=Max(Date) from TD_Time where Date<Cast((Year(@DateDebut)) as varchar)+'0101' and 
      WeekDayLib not in ('Sunday','Saturday') 

---- 0,1,2,3 Begin
Set @Instruction1=
'select pp.Dat as Date,ptf.Lib as PtfLib, pt.Lib, Isin,DevCot, pp.Price,'

---- 0,3 Begin
Set @Instruction2 = 'pp.Aum,pp.AumEur,'

---- 0,1,2,3 Begin
Set @Instruction3 = 'bp.Price bench,'

---- 0 Begin
Set @Instruction4 =
' ptf.Cod,
pt.PtsId,ptf.PtfId,
ptf.Gest,ptf.IsDedicatedFund, 
case 
  when ptf.Gest=''Actions'' and ptf.IsDedicatedFund=0 then 1
  when ptf.Gest=''Diversifiée'' and ptf.IsDedicatedFund=0 then 2 
  when ptf.Gest=''Taux'' and ptf.IsDedicatedFund=0 then 3
  when ptf.Gest=''Profilée'' and ptf.IsDedicatedFund=0 then 4 
  when ptf.IsDedicatedFund=1 then 5
end Classe,
ti.WeekDayLib,ti.MonthYear_Lib,ti.Annee '

---- 0,1,2,3 Begin
Set @Instruction5 =
' into #VL_BENCHMARK
from TF_PartPrice pp  
inner join TD_Time ti on ti.Date=pp.Dat
inner join TD_Part pt on pp.PtsId=pt.PtsId 
left join TF_BenchmarkPrice bp on pp.Dat=bp.Dat and bp.TypPosId=3 and bp.IsCurCtb=1  and bp.PtsId=pp.PtsId 
inner join TD_Portefeuille ptf on pt.PtfId=ptf.PtfId 
where 
  pp.IsCurCtb=1 and pp.TypPosId=3 and 
  ptf.Statut=1 and 
  pp.Dat between ' + convert(varchar(10), @FinAnneePrecedente, 120)+ ' and ' + convert(varchar(10), @DateFin, 120)  + ' and
  ptf.IsTraded=1

if ('+@Frequence+'=''q'') 
begin
    insert into #TEMP_VL1 (PtsId,Date ) 
    select PtsId,max(Date) 
    from #VL_BENCHMARK
    group by  PtsId, Date
end 

if ('+@Frequence+'=''m'') 
  begin
    insert into #TEMP_VL1 (PtsId,Date ) 
    select PtsId,max(Date) 
    from #VL_BENCHMARK
    group by  PtsId,MonthYear_Lib
  end 

if ('+@Frequence+'=''a'') 
begin
    insert into #TEMP_VL1 (PtsId,Date ) 
    select PtsId,max(Date) 
    from #VL_BENCHMARK
    group by  PtsId, Annee
end 

if ('+@Frequence+'=''w'') 
begin
    insert into #TEMP_VL1 (PtsId,Date ) 
    select PtsId,max(Date) 
    from #VL_BENCHMARK
    where DATEPART(weekday,Date) in (2,3,4,5)
    group by  PtsId, DatePart(week,Date), DATEPART(year,Date)
end'

---- 0,3 Begin
Set @Instruction6 =
' select Date, PtfId,sum(AumEur) PtfAumEur
into #SOMME_AUM_EUR
from #VL_BENCHMARK  
group by Date, PtfId '

---- 0,1,2,3 Begin
Set @Instruction7=
' select  
        vlbench1.Date,
        vlbench1.PtfLib,
        vlbench1.Lib,
        vlbench1.Isin,
        vlbench1.DevCot,
        vlbench1.Price ,'

---- 0,3 Begin
Set @Instruction8 =
        ' vlbench1.Aum,
        vlbench1.AumEur,'

---- 0,1,2,3 Begin
Set @Instruction9 = 
        ' isnull(vlbench1.bench,0)bench,'

---- 0 Begin
Set @Instruction10 =
        ' vlbench1.Cod,
        vlbench1.PtsId,
        vlbench1D.Price as PreviousPrice,
        vlbench1D.bench as PreviousBench,
        vlbenchYTD.Price as PreviousYearPrice,
        vlbenchYTD.bench as PreviousYearBench,
        vlbenchMTD.Price as PreviousMonthPrice,
        vlbenchMTD.bench as PreviousMonthBench,
        vlbenchWTD.Price as PreviousWeekPrice,
        vlbenchWTD.bench as PreviousWeekBench,
        vlbenchQTD.Price  as PreviousQuaterPrice,
        vlbenchQTD.bench as PreviousQuaterBench,
        vlbenchFirstDispo.Price as FirstPriceDispo,
        vlbenchFirstDispo.bench as FirstBenchDispo,'

---- 0,1 Begin
Set @Instruction11 =
        ' (vlbench1.Price-isnull(vlbench1D.Price,vlbenchFirstDispo.Price))/isnull(vlbench1D.Price,vlbenchFirstDispo.Price) as VLPerf1Day,
        (vlbench1.Price-isnull(vlbenchWTD.Price,vlbenchFirstDispo.Price))/isnull(vlbenchWTD.Price,vlbenchFirstDispo.Price) as VLPerfWTD,
        (vlbench1.Price-isnull(vlbenchMTD.Price,vlbenchFirstDispo.Price))/isnull(vlbenchMTD.Price,vlbenchFirstDispo.Price) as VLPerfMTD,
        (vlbench1.Price-isnull(vlbenchQTD.Price,vlbenchFirstDispo.Price))/isnull(vlbenchQTD.Price,vlbenchFirstDispo.Price) as VLPerfQTD,
        (vlbench1.Price-isnull(vlbenchYTD.Price,vlbenchFirstDispo.Price))/isnull(vlbenchYTD.Price,vlbenchFirstDispo.Price) as VLPerfYTOD,
        (vlbench1.bench-isnull(vlbench1D.bench,vlbenchFirstDispo.bench))/isnull(vlbench1D.bench,vlbenchFirstDispo.bench) as BenchPerf1Day,
        (vlbench1.bench-isnull(vlbenchWTD.bench,vlbenchFirstDispo.bench))/isnull(vlbenchWTD.bench,vlbenchFirstDispo.bench) as BenchPerfWTD,
        (vlbench1.bench-isnull(vlbenchMTD.bench,vlbenchFirstDispo.bench))/isnull(vlbenchMTD.bench,vlbenchFirstDispo.bench) as BenchPerfMTD,
        (vlbench1.bench-isnull(vlbenchQTD.bench,vlbenchFirstDispo.bench))/isnull(vlbenchQTD.bench,vlbenchFirstDispo.bench) as BenchPerfQTD,
        (vlbench1.bench-isnull(vlbenchYTD.bench,vlbenchFirstDispo.bench))/isnull(vlbenchYTD.bench,vlbenchFirstDispo.bench) as BenchPerfYTOD,'

---- 0,2 Begin
Set @Instruction12 =        
        ' (vlbench1.Price-isnull(vlbench1D.Price,vlbenchFirstDispo.Price))/isnull(vlbench1D.Price,vlbenchFirstDispo.Price) - (vlbench1.bench-isnull(vlbench1D.bench,vlbenchFirstDispo.bench))/isnull(vlbench1D.bench,vlbenchFirstDispo.bench) as RelativePerf1D,
        (vlbench1.Price-isnull(vlbenchYTD.Price,vlbenchFirstDispo.Price))/isnull(vlbenchYTD.Price,vlbenchFirstDispo.Price) - (vlbench1.bench-isnull(vlbenchYTD.bench,vlbenchFirstDispo.bench))/isnull(vlbenchYTD.bench,vlbenchFirstDispo.bench) as RelativePefYTOD,
        (vlbench1.Price-isnull(vlbenchMTD.Price,vlbenchFirstDispo.Price))/isnull(vlbenchMTD.Price,vlbenchFirstDispo.Price) - (vlbench1.bench-isnull(vlbenchMTD.bench,vlbenchFirstDispo.bench))/isnull(vlbenchMTD.bench,vlbenchFirstDispo.bench) as RelativePerfMTD,
        (vlbench1.Price-isnull(vlbenchWTD.Price,vlbenchFirstDispo.Price))/isnull(vlbenchWTD.Price,vlbenchFirstDispo.Price) - (vlbench1.bench-isnull(vlbenchWTD.bench,vlbenchFirstDispo.bench))/isnull(vlbenchWTD.bench,vlbenchFirstDispo.bench) as RelativePerfWTD,
        (vlbench1.Price-isnull(vlbenchQTD.Price,vlbenchFirstDispo.Price))/isnull(vlbenchQTD.Price,vlbenchFirstDispo.Price) - (vlbench1.bench-isnull(vlbenchQTD.bench,vlbenchFirstDispo.bench))/isnull(vlbenchQTD.bench,vlbenchFirstDispo.bench) as RelativePerfQTD,'

---- 0,3 Begin        
Set @Instruction13 =        
        ' sumAumEur.PtfAumEur,'

---- 0 Begin
Set @Instruction14 =       
        ' vlbench1.Gest,
        vlbench1.IsDedicatedFund,
        vlbench1.Classe'

---- 0,1,2,3 Begin
Set @Instruction15 = 
' from #VL_BENCHMARK vlbench1 
left join #VL_BENCHMARK vlbench1D on vlbench1D.PtsId=vlbench1.PtsId and vlbench1D.Date=(select max(Date) from #VL_BENCHMARK where PtsId=vlbench1.PtsId and Date<vlbench1.Date)
left join #VL_BENCHMARK vlbenchYTD on vlbenchYTD.PtsId=vlbench1.PtsId and vlbenchYTD.Date=(select max(Date) from #VL_BENCHMARK where PtsId=vlbench1.PtsId and Date<DATEADD(yy,DATEDIFF(yy,0,vlbench1.Date),0) ) 
left join #VL_BENCHMARK vlbenchMTD on vlbenchMTD.PtsId=vlbench1.PtsId and vlbenchMTD.Date=(select max(Date) from #VL_BENCHMARK where PtsId=vlbench1.PtsId and Date<DATEADD(mm,DATEDIFF(mm,0,vlbench1.Date),0))
left join #VL_BENCHMARK vlbenchWTD on vlbenchWTD.PtsId=vlbench1.PtsId and vlbenchWTD.Date=(select max(Date) from #VL_BENCHMARK where PtsId=vlbench1.PtsId and Date<vlbench1.Date and datename(dw,Date)=''Thursday'')
left join #VL_BENCHMARK vlbenchQTD on vlbenchQTD.PtsId=vlbench1.PtsId and vlbenchQTD.Date=(select max(Date) from #VL_BENCHMARK where PtsId=vlbench1.PtsId and Date<DateAdd(qq,DATEPART(Quarter ,vlbench1.Date)-1 ,DATEADD(yy,DATEDIFF(yy,0,vlbench1.Date),0)))'

---- 0,1,2,3 Begin
Set @Instruction16 =
' left join #VL_BENCHMARK vlbenchFirstDispo on vlbenchFirstDispo.PtsId=vlbench1.PtsId and vlbenchFirstDispo.Date=(select min(Date) from #VL_BENCHMARK where PtsId=vlbench1.PtsId and Date>DATEADD(yy,DATEDIFF(yy,0,vlbench1.Date),0))'

---- 0,3 Begin
Set @Instruction17 =
' left join #SOMME_AUM_EUR sumAumEur on sumAumEur.PtfId=vlbench1.PtfId and sumAumEur.Date=vlbench1.Date '

---- 0,1,2,3 Begin
Set @Instruction18 =
' inner join #TEMP_VL1 tpvl1 on vlbench1.Date=tpvl1.Date and vlbench1.PtsId=tpvl1.PtsId '

-- 0,1,2,3 Begin
Set @Instruction19 =
' where vlbench1.Date between '+ convert(varchar(10), @DateDebut, 120)  +' and ' +  convert(varchar(10), @DateFin, 120)  +' 
  and ( '+ CAST(@Part AS VARCHAR(25)) +' = -1 or  ('+ CAST(@Part AS VARCHAR(25)) +' <> -1 and vlbench1.PtsId='+CAST(@Part AS VARCHAR(25))+'))
  and ('+@Devise+' = ''All'' or (' + @Devise+' <> ''All'' and vlbench1.DevCot = '+@Devise+' )) 
  and ('+CAST(@PtfId AS VARCHAR(25))+' =-1 or ('+ CAST(@PtfId AS VARCHAR(25))+' <>-1 and vlbench1.PtfId='+CAST(@PtfId AS VARCHAR(25))+'))
order by vlbench1.Date,vlbench1.Classe,vlbench1.PtfLib,vlbench1.Lib
drop table #VL_BENCHMARK 
'

-- 0,3 Begin 
Set @Instruction20 =
' drop table #SOMME_AUM_EUR'

-- 0,1,2,3 Begin 
Set @Instruction21 =
' drop table #TEMP_VL1'


Set @DefaultQuery = @Instruction1+@Instruction2+@Instruction3+@Instruction4+
@Instruction5+@Instruction6+@Instruction7+@Instruction8+@Instruction9+@Instruction10+@Instruction11+
@Instruction12+@Instruction13+@Instruction14+
@Instruction15+@Instruction16+@Instruction17+@Instruction18+@Instruction19+@Instruction20+@Instruction21



if(@TypePerf=0)
BEGIN
    Exec (@DefaultQuery)
END


exec P_Moa_GetAumNavBenchOnPtsId 
@DateDebut ='1-10-2012',@DateFin ='1-10-2013',@Part=-1,@Devise ='All',@PtfId =-1,@Frequence ='w', @TypePerf =0