22,209
社区成员
发帖
与我相关
我的任务
分享
create proc zfpro
@kssj nvarchar(50),@jssj nvarchar(50),@hsxz nvarchar(50) --输入参数三个
as
declare @count int,@ksjg varchar(50),@jsjg varchar(50),@cj varchar(50),@i int,@zzf varchar(50),@xgpname nvarchar(50),@xgpid nvarchar(50),@xdp nvarchar(50),@zcjl nvarchar(50),@zhsl nvarchar(50),@dpp nvarchar(50)
select @count=count(id) from gpinfo
create table #lsb(id int,zzf varchar(50),xgpid nvarchar(50),xgpname nvarchar(50),xdp nvarchar(50),zcjl nvarchar(50),zhsl nvarchar(50)) --创建临时表
declare @id int
declare curA cursor for select id from gpinfo
open curA
fetch next from curA
into @id
while @@fetch_status = 0
begin
select @xgpid=gpid ,@xgpname=gpname,@xdp=husheng from gpinfo where id=@id ----根据ID查询基本信息
select @ksjg=spj from gpeinfo where dat=@kssj and gpid=@xgpid ---起始日收盘价
select @jsjg=spj from gpeinfo where dat=@jssj and gpid=@xgpid ----终止日收盘价
select @zcjl=sum(convert(float,cast(cjl as float))),@zhsl=sum(convert(float,cast(hsl as float))) from gpeinfo where gpid=@xgpid --查询总成交量、总换手率
set @cj=convert(float,cast(@jsjg as float))-convert(float,cast(@ksjg as float)) ----查询2日涨幅
set @zzf=(convert(float,cast(@jsjg as float))-convert(float,cast(@ksjg as float)))/convert(float,cast(@ksjg as float)) --查询涨幅%
insert into #lsb(id,zzf,xgpid,xgpname,xdp,zcjl,zhsl) values (@id,@zzf,@xgpid,@xgpname,@xdp,@zcjl,@zhsl) --向临时表插入数据
FETCH NEXT FROM curA INTO @id ---下一条
end
close curA
deallocate curA
if (@hsxz='深市') --三种情况下 执行的sql语句不同
begin
select * from #lsb where xdp='深市' order by xgpid desc
end
else if ( @hsxz='沪市' )
begin
select * from #lsb where xdp='沪市' order by xgpid desc
end
else
begin
select * from #lsb where xdp='深市' or xdp='沪市' order by xgpid desc
end
go
dim kdat,zdat,hss1,sqlproc,rs
kdat="2011-2-17"
zdat="2011-2-18"
hss1=""
sqlproc = "EXECUTE zfpro '"&kdat&"','"&zdat&"','"&hss1&"'"
response.Write sqlproc
Set rs = cnn.Execute(sqlproc)
response.Write rs("xgpid") '输出第一行的一个ID 。测试所用