存储过程出错，连接也报关闭。

[janesquestion]

CREATE procedure huawei_dest
@first_yy char(4), @first_mm char(2), @first_dd char(2),
@first_hh char(2),@end_hh char(2),
@scope char(12)
as
begin
declare @first_time varchar(20),@last_time varchar(20),@table_name char(15),@temp_hh int
declare @mysql varchar(1000)
create table #huawei_detail(called char(8),owner int,call_times int,cduration int,occur_time datetime null)

select @temp_hh=convert(int,@first_hh)
if (@temp_hh<10) select @first_hh='0'+convert(char(1),@temp_hh)
else select @first_hh=convert(char(2),@temp_hh)
select @temp_hh=convert(int,@end_hh)
if (@temp_hh<10) select @end_hh='0'+convert(char(1),@temp_hh)
else select @end_hh=convert(char(2),@temp_hh)



select @last_time=@first_yy + '-' + @first_mm + '-' + @first_dd + ' ' + @end_hh + ':00:00'
select @first_time=@first_yy + '-' + @first_mm + '-' + @first_dd + ' ' + @first_hh + ':00:00'
select @table_name='upcdr06' + @first_mm

if (@scope='0')
begin
select @mysql='insert into #huawei_detail select called,owner,count(*),sum(cduration),occur_time from '+ @table_name +' where occur_time>='''+ @first_time +''' and occur_time<='''+ @last_time + ''' group by called,owner,occur_time'
end
else
begin
select @mysql='insert into #huawei_detail select called,owner,count(*),sum(cduration),occur_time from '+ @table_name +' where occur_time>='''+ @first_time +''' and occur_time<='''+ @last_time +''' and called ='''+ @scope +''' group by called,owner,occur_time'
end
exec(@mysql)


insert into #huawei_detail select called,20,sum(call_times),sum(cduration),occur_time from #huawei_detail group by called,occur_time


insert into #huawei_detail select called,30,sum(call_times),sum(cduration),'2006-09-01 1:00:00' from #huawei_detail where owner=20 group by called

select * from #huawei_detail order by called,occur_time,owner

end

最后那句insert有问题，报"将 expression 转换为数据类型 int 时发生算术溢出错误。"我感觉是那个日期格式可能有问题。把这行注释掉就可以在SQL查询器里正确输出。

[janesquestion]

但是，既使不要那一行，就是说在SQL查询器里可以查出结果，用ASP程序后，还是报ADODB.Recordset 错误 '800a0e78'

对象关闭时，不允许操作。

ASP调用程序为
<%set conn=server.CreateObject("ADODB.connection")
conn.ConnectionString="Provider=SQLOLEDB.1;Data Source=136.5.63.143;pwd=cqnmc;uid=sa;Initial Catalog=CQNMC"
conn.Open
%>
<%set rs=server.createobject("adodb.recordset")
sql="ss7..huawei_dest " & first_yy_string & "," & first_mm_string & "," & first_dd_string & "," & first_hh_string & "," & end_hh_string & "," & scopestr
conn.CommandTimeout = 18000
Server.ScriptTimeout = 18000

set rs = conn.execute(sql)

%>
<%if rs.eof then
%>

执行到if那一行时报的错。

[zicxc]

sum(call_times),sum(cduration)
两个的值书不是超出了int的范围
-2147483648 到 2147483647

[wuya8115]

有个问题，能对时间直接求和吗？sum(call_times)?

最好用convert()转换一下数据类型。