create proc zfpro
@kssj varchar(50),@jssj varchar(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(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 @idwhile @@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(zzf,xgpid,xgpname,xdp,zcjl,zhsl) values (@zzf,@xgpid,@xgpname,@xdp,@zcjl,@zhsl) --向临时表插入数据
FETCH NEXT FROM curA INTO @id ---下一条
end
close curA
deallocate curA if (@hsxz='深市')
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
endgo
如上面的 存储过程。 我的ASP 程序是这样写的 sqlproc = "EXECUTE dbo.zfpro " & "'" & kdat & "'," & "'" & zdat & "'," & "'" & hss1 & "'"
response.Write sqlproc
Set rs = cnn.Execute(sqlproc) rs("id")
rs("name")
.......
就是读出 存储过程中创建的临时表的数据想读出来存储过程中创建的临时表的数据 可是 什么都读不出来。我在 查询分析器里 运行存储过程的时候没错误。为什么 读取不出数据! 求教!
如果有多表,表之间如何关联?
发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
@kssj varchar(50),@jssj varchar(50),@hsxz nvarchar(50)as
set nocount on
/*
...
原封不动
*/
set nocount off
go
exec sp_recompile N'zfpro'
重连
response.Write sqlproc字符串打印出来是怎样的?
打印出来很正常: EXECUTE dbo.zfpro '2011-2-17','2011-2-18',''
--#2. 存储过程检查过了,没看出问题:1.在查询分析器中执行有结果吗?2.用代码调用有结果吗?
--#3. 看一下是否代码有问题cnn.Execute(sqlproc),这样能返回结果集吗?. 在C#中用(VB自己顶一下):
SqlConnection sqlconn = new SqlConnection();
sqlconn.ConnectionString = "省略";
sqlconn.Open();
SqlCommand sqlcomm = new SqlCommand();
sqlcomm.CommandType = CommandType.StoredProcedure;
sqlcomm.Connection = sqlconn;
sqlcomm.CommandText = "xxxx";
SqlDataAdapter sqlda = new SqlDataAdapter(sqlcomm);
DataSet ds = new DataSet();
sqlda.Fill(ds);
if (ds.Tables.Count > 0)
{
//Do sth ds.Tables[0]
//Do sth ds.Tables[1]
}