CREATE PROCEDURE [compute] @sum1 real output,@sum3 real output AS declare @a varchar(100),@i int,@b varchar(100) ,@sql nvarchar(4000) --这里要定义为nvarchar类型 ,@sum real ,@sum2 real set @sum=0 set @sum1=0 set @sum2=0 set @sum3=0 set @i=day(getdate()) while @i>0 begin set @a='d'+convert(char(8),getdate()-@i+1,112) --得到时间表名 set @b=convert(char(8),getdate()-@i+1,112) if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].['+@a+']') and OBJECTPROPERTY(id, N'IsUserTable') = 1) begin --print @a set @sql='select @sum=sum(首站总供水流量),@sum2=sum(首站总供压) from '+@a+' where 采集时间='+@b+'0800 or 采集时间='+@b+'1600 or 采集时间 ='+@b+'2400' --动态得到表 exec sp_executesql @sql,N'@sum real output,@sum2 real output',@sum output,@sum2 output set @sum1=isnull(@sum,0)+@sum1 --不知道你是如何调用的,如果@sum1无初始值的话,得不到正确结果.所以要判断一下,@sum1是否为null,因为null与任何值的计算结果均为null. set @sum3=isnull(@sum2,0)+@sum3 set @i=@i-1 end else set @i=@i-1 end GO
@sum1 real output,@sum3 real output
AS
declare @a varchar(100),@i int,@b varchar(100)
,@sql nvarchar(4000) --这里要定义为nvarchar类型
,@sum real
,@sum2 real
set @sum=0
set @sum1=0
set @sum2=0
set @sum3=0
set @i=day(getdate())
while @i>0
begin
set @a='d'+convert(char(8),getdate()-@i+1,112) --得到时间表名
set @b=convert(char(8),getdate()-@i+1,112)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].['+@a+']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
--print @a
set @sql='select @sum=sum(首站总供水流量),@sum2=sum(首站总供压) from '+@a+' where 采集时间='+@b+'0800 or 采集时间='+@b+'1600 or 采集时间 ='+@b+'2400' --动态得到表
exec sp_executesql @sql,N'@sum real output,@sum2 real output',@sum output,@sum2 output
set @sum1=isnull(@sum,0)+@sum1 --不知道你是如何调用的,如果@sum1无初始值的话,得不到正确结果.所以要判断一下,@sum1是否为null,因为null与任何值的计算结果均为null.
set @sum3=isnull(@sum2,0)+@sum3
set @i=@i-1
end
else
set @i=@i-1
end
GO