proc上的动态sql错误 [英] Dynamic sql error on a proc

查看：60
本文介绍了proc上的动态sql错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

你好，

使用sql server 2008，存储过程有问题，当它不是动态sql格式时工作正常。

错误信息是消息156 ，等级15，状态1，行99

关键字'All'附近的语法不正确。

代码下方

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

推荐答案

嘿那里，


您错过了 @Device 附近的单引号 Set @ Instruction19 部分。



试试这样：

Hey there,

You missed single quotes around @Device in Set @Instruction19 part.

Try like this:
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 
'





让我知道它是否有帮助。



注意：您可以使用 print 来查看查询的来源结果，例如， print @ Instruction19



Azee ......



Let me know if it helps.

Note: You can use print to see how the query is gonna turn out, e.g, print @Instruction19

Azee...


这篇关于proc上的动态sql错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆