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
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 @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)
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')
Set @Instruction1=
'select pp.Dat as Date,ptf.Lib as PtfLib, pt.Lib, Isin,DevCot, pp.Price,'
Set @Instruction2 = 'pp.Aum,pp.AumEur,'
Set @Instruction3 = 'bp.Price bench,'
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 '
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'
Set @Instruction6 =
' select Date, PtfId,sum(AumEur) PtfAumEur
into #SOMME_AUM_EUR
from #VL_BENCHMARK
group by Date, PtfId '
Set @Instruction7=
' select
vlbench1.Date,
vlbench1.PtfLib,
vlbench1.Lib,
vlbench1.Isin,
vlbench1.DevCot,
vlbench1.Price ,'
Set @Instruction8 =
' vlbench1.Aum,
vlbench1.AumEur,'
Set @Instruction9 =
' isnull(vlbench1.bench,0)bench,'
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,'
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,'
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,'
Set @Instruction13 =
' sumAumEur.PtfAumEur,'
Set @Instruction14 =
' vlbench1.Gest,
vlbench1.IsDedicatedFund,
vlbench1.Classe'
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)))'
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))'
Set @Instruction17 =
' left join #SOMME_AUM_EUR sumAumEur on sumAumEur.PtfId=vlbench1.PtfId and sumAumEur.Date=vlbench1.Date '
Set @Instruction18 =
' inner join #TEMP_VL1 tpvl1 on vlbench1.Date=tpvl1.Date and vlbench1.PtsId=tpvl1.PtsId '
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
'
Set @Instruction20 =
' drop table #SOMME_AUM_EUR'
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