我知道报错的原因了。 因为我需要sum一个qty的值,但因为临时表中qty有null值,所以报warning 程序如下: declare @t01 numeric select @t01=(select sum(qty) from #pre_temp where type='01') 如果在创建临时表的时候定义qty不为null,并设置默认值为0,不知道是不是有用。 该怎么定义?
--这样的方法可行.定义临时表的方法:create table #pre_temp(qty int not null default 0)
现在运行存储过程没有warning了,但是在程序里还是报超时的错 天呀,怎么办,一直搞不定
declare @sql varchar(5000) select @sql = 'insert into pre_temp select '''+@rand+''''+' as random,type,1 as type1,0 as total' select @sql = @sql + ',sum(case m when '''+m+''' then qty else 0 end) ['+m+']'+'' from (select distinct m from #pre_temp) as a select @sql = @sql+' from #pre_temp group by type' exec(@sql) drop table #pre_temp運行這段程序就會出現warning
--改成这样就不会有警告了,不过改不改的结果都一样,个人觉得没必要改.declare @sql varchar(5000) select @sql = 'insert into pre_temp select '''+@rand+''''+' as random,type,1 as type1,0 as total' select @sql = @sql + ',sum(case m when '''+m+''' then isnull(qty,0) else 0 end) ['+m+']'+'' from (select distinct m from #pre_temp) as a select @sql = @sql+' from #pre_temp group by type' exec(@sql) drop table #pre_temp
select a,sum(case b when 1 then c else 0 end) from 表 group by a
select a,sum(case b when 1 then c else 0 end) from 表 group by a
超时:
1:企业管理器->服务器->属性->连接->查询超时(改为0或一个较大的数)
2:ADO的连接超时属性connectiontimeout 设大点.
3:ADO的CommandTimeout设大点
你的方法我已经用过了,可是还是会报超时的错To 邹建:
我用isnull来判断还是会出现warning
isnull跟你的语句应该作用一样吧
就是说,用isnull的话,是先产生了警告.再到你的isnull处理的,所以两者是不同的.
将你的代码贴出来.
因为我需要sum一个qty的值,但因为临时表中qty有null值,所以报warning
程序如下:
declare @t01 numeric
select @t01=(select sum(qty) from #pre_temp where type='01')
如果在创建临时表的时候定义qty不为null,并设置默认值为0,不知道是不是有用。
该怎么定义?
天呀,怎么办,一直搞不定
select @sql = 'insert into pre_temp select '''+@rand+''''+' as random,type,1 as type1,0 as total'
select @sql = @sql + ',sum(case m when '''+m+''' then qty else 0 end) ['+m+']'+''
from (select distinct m from #pre_temp) as a
select @sql = @sql+' from #pre_temp group by type'
exec(@sql)
drop table #pre_temp運行這段程序就會出現warning
select @sql = 'insert into pre_temp select '''+@rand+''''+' as random,type,1 as type1,0 as total'
select @sql = @sql + ',sum(case m when '''+m+''' then isnull(qty,0) else 0 end) ['+m+']'+''
from (select distinct m from #pre_temp) as a
select @sql = @sql+' from #pre_temp group by type'
exec(@sql)
drop table #pre_temp