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表我想报年份改成我输入的参数。怎么改啊存储select
@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表我想报年份改成我输入的参数。怎么改啊存储select
@TableName varchar(50)
as
Exec('select * from ' + @TableName )exec GetDataByTableName 'TableName'
@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;
@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;