【急急急】关于SQL 2000存储过程调用的问题

[sqq4290]

我写了一个车次换乘查询的存储过程A（数据库是SQL Server2000)，A存储过程执行的时候还会调用B存储过程两次，B存储过程主要是返回"车次id"作为A查询的条件，两个表里都用了临时表和表变量（@t table(...)）来暂存中间查询结果，现在出现一个问题：不能直接执行存储过程A必须要单独执行存储过程B后再执行A才会成功，而且如果停止调用B存储过程10分钟以上再次调用A又会失败(以下是查询分析器报的错)：
服务器: 消息 207，级别 16，状态 3，过程 getHC_0，行 10
列名 'e_stop' 无效。
服务器: 消息 207，级别 16，状态 1，过程 getHC_0，行 10
列名 'num' 无效。
服务器: 消息 207，级别 16，状态 1，过程 getHC_0，行 10
列名 'bus_id' 无效。
服务器: 消息 207，级别 16，状态 3，过程 getHC_0，行 10
列名 'e_stop' 无效。
服务器: 消息 207，级别 16，状态 1，过程 getHC_0，行 10
列名 'num' 无效。
服务器: 消息 207，级别 16，状态 1，过程 getHC_0，行 10
列名 'bus_id' 无效。

我用JDBC来调用存储过程A会报错（eclipse 控制台上报的错)：
2008-6-8 14:37:40 org.apache.catalina.core.StandardWrapperValve invoke
严重: Servlet.service() for servlet BusLineServlet threw exception
java.lang.OutOfMemoryError: Java heap space

--存储过程A
CREATE proc dbo.getHC_0
(@start_stop int,@end_stop int)
as
begin
declare @e_stop_name varchar(50)
set @e_stop_name=dbo.getStopName(@end_stop)
select  top 5 A.bus_id,e_stop=@e_stop_name,num=ABS(A.req-B.req) into #t from (select bus_id,req from Chengdu_BSR where stop_id = @start_stop)A,
(select bus_id,req from Chengdu_BSR where stop_id = @end_stop)B
where A.bus_id = B.bus_id order by num 
select bus_name,e_stop,num from #t,Chengdu_bus where #t.bus_id=Chengdu_bus.bus_id
end
GO

存储过程B
CREATE proc getHC_1
(@start_stop varchar(50),@end_stop varchar(50))
as
begin
declare @t table(m_state varchar(50),e_state varchar(50),bus_name1 varchar(20),bus_name2 varchar(20),num1 int,num2 int)--总路线表
declare @temp_stop int --中转站ID
declare @s_stop int
declare @e_stop int
set @s_stop=dbo.getStopId(@start_stop)
set @e_stop=dbo.getStopId(@end_stop)
print @s_stop
print @e_stop

--得到中转站表#t
select A.stop_id into #t from(select distinct stop_id from Chengdu_BSR  where bus_id in
(select bus_id from Chengdu_BSR  where stop_id = @s_stop))A,
(select distinct stop_id from Chengdu_BSR  where bus_id in
(select bus_id from Chengdu_BSR  where stop_id = @e_stop))B where A.stop_id= B.stop_id

--使用游标遍历中转站表#t
create table #t1(bus_name varchar(20),e_stop varchar(50),num int)
create table #t2(bus_name varchar(20),e_stop varchar(50),num int)
declare mstop_cursor cursor for select * from #t 
open mstop_cursor 
fetch next from mstop_cursor into @temp_stop 
while @@fetch_status=0 
begin 
    truncate table #t1 
    truncate table #t2 
    insert into #t1 exec dbo.getHC_0 @s_stop,@temp_stop --返回bus_id,e_stop,num并填充进#t1 
    insert into #t2 exec dbo.getHC_0 @temp_stop,@e_stop --返回bus_id,e_stop,num并填充进#t2 
    insert @t select #t1.e_stop as m_state,#t2.e_stop as e_state,#t1.bus_name as bus_name1,#t2.bus_name as bus_name2,#t1.num as num1,#t2.num as num2 
from #t1 cross join #t2  --返回通过此个中转站的所有可能路线 

    fetch next from mstop_cursor into @temp_stop 
end 
close mstop_cursor 
deallocate mstop_cursor 
select top 12 start_stop=@start_stop,end_stop=e_state,m_stop=m_state,bus_name1,bus_name2,total_stops=num1+num2 into #x from @t
order by total_stops 
select identity(int,1,1)as line_id,start_stop,end_stop,m_stop,bus_name1,bus_name2,total_stops into #Y from #x
select * from #Y
end
GO

Q:请大家帮看看可能是什么原因引起这样的结果？这个是我毕业设计的一部分，花了两个月时间天天坐在电脑前写代码，进度太慢了，工作还没着落，心里很急，希望大家帮帮忙，谢谢！

[duanzhi1984]

沙发

[-狙击手-]

局部临时表,改成全局的就可以了

[duanzhi1984]

已经上了3个小时了，有点累了。期待楼下解决

[正宗老冉]

存储过程A，用不了你那个存储过程B产生的局部临时表。

[duanzhi1984]

程序就是这样，只要一点点小小的问题都能难到你

[-狙击手-]

你可以在过程A中调用过程B，把B的结果生成某个表中，

CREATE proc dbo.getHC_0
(@start_stop int,@end_stop int)
as
begin
declar @t table(......)
insert @t exec getHC_1 ...
declare @e_stop_name varchar(50)
set @e_stop_name=dbo.getStopName(@end_stop)
select top 5 A.bus_id,e_stop=@e_stop_name,num=ABS(A.req-B.req) into #t from (select bus_id,req from Chengdu_BSR where stop_id = @start_stop)A,
(select bus_id,req from Chengdu_BSR where stop_id = @end_stop)B
where A.bus_id = B.bus_id order by num
select bus_name,e_stop,num from #t,Chengdu_bus where #t.bus_id=Chengdu_bus.bus_id
end
GO

[sqq4290]

楼上的意思就是用@t table来代替 #t?

[zhiguo2008]

同意6楼的。

[fcuandy]

回复内容见
http://topic.csdn.net/u/20080615/13/5fc207de-93b4-459f-9507-5e8b5946c855.html?seed=2099740936

[susean]

你可以考虑看看SELECT语句再整理一下能不能在一个PROC里完成
如果坚持要返回表的话可以试试用FUNCTION返回表,不过只能是返回一个表

create function test(@start_stop int,@end_stop int)
returns table
as
return(
--select 语句
)

--在存储过程里直接用
insert into #t1
select * from dbo.test(@s_stop,@temp_stop)

[sqq4290]

[Quote=引用 6 楼 happyflystone 的回复:]
你可以在过程A中调用过程B，把B的结果生成某个表中，

CREATE proc dbo.getHC_0
(@start_stop int,@end_stop int)
as
begin
declar @t table(......)
insert @t exec getHC_1 ...
declare @e_stop_name varchar(50)
set @e_stop_name=dbo.getStopName(@end_stop)
select top 5 A.bus_id,e_stop=@e_stop_name,num=ABS(A.req-B.req) into #t from (select bus_id,req from Chengdu_BSR where stop_id = @start…
[/Quote]
查询分析器报错：
服务器: 消息 197，级别 15，状态 1，过程 getHC_1，行 31
无法在向表插入变量时将 EXECUTE 用作源。

[hery2002]

局部临时表,改成全局的就可以了

[LIHY70]

有时候会忽略，作为提醒

[Herb2]

你的关键问题在于你的临时表重名了，你的两个#t完全不搭架，应该分开取名。
看来这应该是SQL 的BUG.
主要是在语法检查阶段过不去。

set nocount on
create table tb(id int)
insert tb select 2
go
create proc a
as
begin
 select id,a ='a' into #t from tb
 select id,a from #t
end
go
create proc b
as
begin
 create table #(id int,c char(1))
 select id,b='b' into #t from tb
 exec a
 
end
go
b
go
drop table tb
go
drop proc a
go
drop proc b
/*
服务器: 消息 207，级别 16，状态 3，过程 a，行 5
列名 'a' 无效。
*/

下面的就可以

set nocount on
create table tb(id int)
insert tb select 2
go
create proc a
as
begin
 select id,a ='a' into #t from tb
 exec('select id,a from #t')
end
go
create proc b
as
begin
 select id,b='b' into #t from tb
 exec a
 
end
go
b
go
drop table tb
go
drop proc a
go
drop proc b
/*
id          a    
----------- ---- 
          2 a
*/

[Herb2]

将你其中一个存储过程的#t改名就行了。

[sqq4290]

谢谢大家，这个问题已经解决了