存储过程的表名是不确定的，能不能用字符串拼接啊

flyingsky2008 2013-05-07 01:55:06

create proc P_CRE_TMP
@startDate date, @endDate date
as
begin
declare @date date, @mess varchar(50)
select @date =@startDate
while @date<=@endDate
begin
insert into TJ_PB_ARRIVE_PJ_TAB
select Fld_Print_Date,
S.Fld_Station_Code,
count(*) 批,
sum(Fld_Num) 件
from IQ_BAG_INDEX_TAB_2011 I
join V_STATION_ALL_TAB S on S.Fld_Station_Code = I.Fld_Arrive_Station_Code and S.Fld_Filiale_Code in (select Fld_Filiale_Code from FILIALE_TAB where Fld_Flag<>'9')
where Fld_Print_Date = convert(char(8),@date,112)
and Fld_Waste_Code = '0'
and Fld_Ticket_Type in ('0','1')
group by Fld_Print_Date,S.Fld_Station_Code

select @mess = convert(char(8),@date,112)
print @mess
select @date = dateadd(dd,1,@date)
commit
end
end;
上面的那个IQ_BAG_INDEX_TAB_2011表我想报年份改成我输入的参数。怎么改啊。。。

...全文

264 14 打赏收藏转发到动态举报

写回复

用AI写文章

14 条回复

切换为时间正序

请发表友善的回复…

发表回复

flyingsky2008 2013-05-08

打赏
举报

引用 13 楼 ap0405140 的回复:

原来是sysbase,不懂,没环境,帮不到lz喔.

还是谢谢你。

唐诗三百首 2013-05-07

打赏
举报

原来是sysbase,不懂,没环境,帮不到lz喔.

flyingsky2008 2013-05-07

打赏
举报

引用 11 楼 ap0405140 的回复:

在SQL2008R2环境测试ok吖.. lz再自行调试一下咯,这是动态SQL的方法.

额，我是在sysbase上怎么就不行了呢。

唐诗三百首 2013-05-07

打赏
举报

在SQL2008R2环境测试ok吖.. lz再自行调试一下咯,这是动态SQL的方法.

flyingsky2008 2013-05-07

打赏
举报

引用 9 楼 ap0405140 的回复:

Sorry! 修正一下,


create proc P_CRE_TMP	
@startDate date, 
@endDate date 
as	
begin	
declare @date date,@mess varchar(50),@tsql varchar(6000)
select @date =@startDate	
while @date<=@endDate	
begin
 select @tsql='
  insert into TJ_PB_ARRIVE_PJ_TAB	
  select Fld_Print_Date,	
         S.Fld_Station_Code,
         count(*) 批,
         sum(Fld_Num) 件
 from IQ_BAG_INDEX_TAB_'+rtrim(datepart(yyyy,@date))+' I	
 join V_STATION_ALL_TAB S on S.Fld_Station_Code = I.Fld_Arrive_Station_Code and S.Fld_Filiale_Code in (select Fld_Filiale_Code from FILIALE_TAB where Fld_Flag<>''9'')
 where Fld_Print_Date ='''+convert(char(8),@date,112)+''' 
       and Fld_Waste_Code = ''0''	
       and Fld_Ticket_Type in (''0'',''1'')	
 group by Fld_Print_Date,S.Fld_Station_Code '	

 exec(@tsql)

  select @mess = convert(char(8),@date,112)	
  print @mess	
  select @date = dateadd(dd,1,@date)	
commit	
end	
end;

不行啊，不能识别‘yyyy’

唐诗三百首 2013-05-07

打赏
举报

Sorry! 修正一下,


create proc P_CRE_TMP	
@startDate date, 
@endDate date 
as	
begin	
declare @date date,@mess varchar(50),@tsql varchar(6000)
select @date =@startDate	
while @date<=@endDate	
begin
 select @tsql='
  insert into TJ_PB_ARRIVE_PJ_TAB	
  select Fld_Print_Date,	
         S.Fld_Station_Code,
         count(*) 批,
         sum(Fld_Num) 件
 from IQ_BAG_INDEX_TAB_'+rtrim(datepart(yyyy,@date))+' I	
 join V_STATION_ALL_TAB S on S.Fld_Station_Code = I.Fld_Arrive_Station_Code and S.Fld_Filiale_Code in (select Fld_Filiale_Code from FILIALE_TAB where Fld_Flag<>''9'')
 where Fld_Print_Date ='''+convert(char(8),@date,112)+''' 
       and Fld_Waste_Code = ''0''	
       and Fld_Ticket_Type in (''0'',''1'')	
 group by Fld_Print_Date,S.Fld_Station_Code '	

 exec(@tsql)

  select @mess = convert(char(8),@date,112)	
  print @mess	
  select @date = dateadd(dd,1,@date)	
commit	
end	
end;

唐诗三百首 2013-05-07

打赏
举报

try this,


create proc P_CRE_TMP	
@startDate date, 
@endDate date 
as	
begin	
declare @date date,@mess varchar(50),@tsql varchar(6000)
select @date =@startDate	
while @date<=@endDate	
begin
 select @tsql='
  insert into TJ_PB_ARRIVE_PJ_TAB	
  select Fld_Print_Date,	
         S.Fld_Station_Code,
         count(*) 批,
         sum(Fld_Num) 件
 from IQ_BAG_INDEX_TAB_'+rtrim(datepart(yyyy,@date))+' I	
 join V_STATION_ALL_TAB S on S.Fld_Station_Code = I.Fld_Arrive_Station_Code and S.Fld_Filiale_Code in (select Fld_Filiale_Code from FILIALE_TAB where Fld_Flag<>''9'')
 where Fld_Print_Date ='''+convert(char(8),@date,112)+' 
       and Fld_Waste_Code = ''0''	
       and Fld_Ticket_Type in (''0'',''1'')	
 group by Fld_Print_Date,S.Fld_Station_Code '	

 exec(@tsql)

  select @mess = convert(char(8),@date,112)	
  print @mess	
  select @date = dateadd(dd,1,@date)	
commit	
end	
end;

flyingsky2008 2013-05-07