100分求解在vb中用报表或控件显示执行存储过程后所生成的表的步骤或代码，急急急急急！

yangjian0219 2003-08-25 02:13:28

CREATE procedure RRR3
@BeginDate datetime, --统计起始时间
@EndDate datetime --统计结束时间
as
declare @intDate int --统计总天数
declare @intK int --当前统计天数
declare @datTmpDate datetime --当前统计时间
declare @strYear char(2) --当前统计年
declare @strMonth char(2) --当前统计月
declare @strDay char(2) --当前统计日
declare @strYYMMDD char(6) --当前统计年月日
declare @strTableName char(8) --当前统计表名
declare @strSql varchar --当前统计查询语句

--取出统计总天数及设置当天统计天数为0
set @intDate = datediff(day,@BeginDate,@EndDate)
set @intK = 0
--清除统计数据表中的数据
truncate table tmpCardStat
truncate table tmpCardData
truncate table tmpMerData
truncate table tmpBankData
truncate table tmpCard2Data
drop table tmpmer2data
drop table tmpbank2data
drop table jjj
drop table jjj2
drop table lastone
drop table finlish
drop table jjj3
if @@error = 0 print 'Truncate temp tables succeed, begin stat query...'

--循环取出基础数据表中的数据，进行第一轮统计
while @intK<=@intDate
begin
set @datTmpDate = dateadd(day,@intK,@BeginDate)
set @strYear = right(convert(varchar,year(@datTmpDate)),2)
set @strMonth = right('00'+convert(varchar,month(@datTmpDate)),2)
set @strDay = right('00'+convert(varchar,day(@datTmpDate)),2)
set @strYYMMDD = @strYear + @strMonth + @strDay
set @strTableName = 'L' + @strYYMMDD + 'P'

--按卡号进行统计
exec ('insert into tmpCardStat'+
' select Tran_acc as CardNo,count(*) as TranTimes,Sum(Setm_amt) as TranAmount,Iss_code as BankCode,'''+@strYYMMDD+''' as TranDate'+
' from '+@strTableName+
' where Rep_code=''00'' and Sett_flag=''Y'' group by Tran_acc,Iss_code')
if @@error = 0 print datename(hh,getdate())+':'+datename(mi,getdate())+':'+datename(ss,getdate())+' Query cardholders info from table ['+@strTableName+'] succeed.'

--按商户进行统计
exec ('insert into tmpMerData'+
' select Mer_id as MerNo,count(*) as TranTimes,Sum(Setm_amt) as TranAmount,'''+@strYYMMDD+''' as TranDate'+
' from '+@strTableName+
' where Rep_code=''00'' and Sett_flag=''Y'' group by Mer_id')
if @@error = 0 print datename(hh,getdate())+':'+datename(mi,getdate())+':'+datename(ss,getdate())+' Query merchants info from table ['+@strTableName+'] succeed.'

--按发卡行进行统计
exec ('insert into tmpBankData'+
' select Iss_code as BankCode,count(*) as TranTimes,Sum(Setm_amt) as TranAmount,'''+@strYYMMDD+''' as TranDate'+
' from '+@strTableName+
' where Rep_code=''00'' and Sett_flag=''Y'' group by Iss_code')
if @@error = 0 print datename(hh,getdate())+':'+datename(mi,getdate())+':'+datename(ss,getdate())+' Query issue banks info from table ['+@strTableName+'] succeed.'

set @intK = @intK + 1

--进行第二轮统计
if ( @intK=@intDate+1)
begin
exec ('insert into tmpCardData select CardNo,Sum(TranTimes) as TranTimes,Sum(TranAmount) as TranAmount,BankCode,left(trandate,4) as TranDate
from tmpCardStat group by CardNo,BankCode,left(trandate,4)')
exec ('insert into tmpCard2Data select CardNo,Sum(TranTimes) as TranTimes,Sum(TranAmount) as TranAmount,BankCode,left(trandate,4) as TranDate
from tmpCardStat where TranAmount>10000.00 group by CardNo,BankCode,TranDate')

if @@error = 0
begin
--清除第一轮卡号统计数据表中的数据
truncate table tmpCardStat
print datename(hh,getdate())+':'+datename(mi,getdate())+':'+datename(ss,getdate())+' Query cardholders info from table [tmpCardData] succeed.'
print datename(hh,getdate())+':'+datename(mi,getdate())+':'+datename(ss,getdate())+' Query cardholders info from table [tmpCard2Data] succeed.'

end
end
end
--进行第三轮统计
begin
exec( 'select cardno as cardno,sum(tranamount) as tranamount,sum(trantimes) as trantimes
,sum(case right(trandate,2) when 01 then tranamount end) as [1月金额]
,sum(case right(trandate,2) when 02 then tranamount end) as [2月金额]
,sum(case right(trandate,2) when 03 then tranamount end) as [3月金额]
,sum(case right(trandate,2) when 04 then tranamount end) as [4月金额]
,sum(case right(trandate,2) when 05 then tranamount end) as [5月金额]
,sum(case right(trandate,2) when 06 then tranamount end) as [6月金额]
,sum(case right(trandate,2) when 07 then tranamount end) as [7月金额]
,sum(case right(trandate,2) when 08 then tranamount end) as [8月金额]
,sum(case right(trandate,2) when 09 then tranamount end) as [9月金额]
,sum(case right(trandate,2) when 10 then tranamount end) as [10月金额]
,sum(case right(trandate,2) when 11 then tranamount end) as [11月金额]
,sum(case right(trandate,2) when 12 then tranamount end) as [12月金额]
into jjj from tmpcarddata group by cardno')

exec('select cardno as cardno,count(*) as cs into jjj2 from tmpcard2data group by cardno,bankcode')
exec('select merno as merno,Sum(trantimes) as trantimes,Sum(tranamount) as tranamount into tmpmer2data from tmpmerdata group by merno')
exec ('select bankcode as bankcode,Sum(trantimes) as trantimes,Sum(tranamount) as tranamount into tmpbank2data from tmpbankdata group by bankcode')
end
--进行第四轮统计
begin
exec('select cardno as cardno into jjj3 from jjj2 where cs>=3')
exec('select tmpmer2data.merno as ‘商户号’,tmpmer2data.trantimes as ‘总次数’,tmpmer2data.TranAmount as ‘总金额’,merchant.mer_name as ‘商户名称’ into shanghu from tmpmer2data,merchant where tmpmer2data.merno=merchant.mer_code')
exec('select tmpbank2data.bankcode as‘发卡行代码’,tmpbank2data.trantimes as ‘总次数’,tmpbank2data.tranamount as ‘总金额’,bank.bank_name as ‘发卡行名称’ into fakahang from tmpbank2data,bank where tmpbank2data.bankcode=bank.bank_code')
end
--进行第五轮统计
begin
exec('select jjj.*,@intDate/jjj.‘总次数’ as ‘刷卡平均时间’ into lastone from jjj3,jjj where jjj.cardno=jjj3.cardno')
end
--进行第六轮统计
begin
exec('select lastone.*,bank.bank_name into finlish from lastone,bank where lastone.bankcode=bank.bank_code')
end
GO
在vb中执行该存储过程时：
寻求在vb中用报表或控件显示以上存储过程中的表finlish中的内容（注：表finlish中的列是根据用户所输入的时间段变化的
不是每次都一样）

...全文