create procedure cpc3_proc @consultantno char(5),@startdate char,@total datetype output
as
begin
declare @cpc3code char(8)
declare @startdate_temp char(8)
declare @total_temp int
declare @total int
declare @i int
set @startdate_temp = @startdate + '%'
set @i = 0
set @total_temp = 0
set @total = 0
--select @consultantno as 'consultantno',@startdate as 'startdate'
declare cpc3_cur cursor for select cpc3code from cpc3_view where consultantno like @consultantno
and startdate like @startdate_temp
open cpc3_cur
fetch next from cpc3_cur into @cpc3code
while @@fetch_status = 0
begin
--select @cpc3code as 'cpc1code',@consultantno as 'consultantno'
select @total_temp=total from cpc3_view where cpc3code=@cpc3code and consultantno=@consultantno
set @i = @i + 1
set @total = @total + @total_temp
--select @i as 'i',@total_temp as 'total_temp',@total as 'total'
fetch next from cpc3_cur into @cpc3code
end
close cpc3_cur
deallocate cpc3_cur
if @i = 0
set @total = 0
else
set @total = cast((@total/@i) as int)
select @total as 'ave'end
as
begin
declare @cpc3code char(8)
declare @startdate_temp char(8)
declare @total_temp int
declare @total int
declare @i int
set @startdate_temp = @startdate + '%'
set @i = 0
set @total_temp = 0
set @total = 0
--select @consultantno as 'consultantno',@startdate as 'startdate'
declare cpc3_cur cursor for select cpc3code from cpc3_view where consultantno like @consultantno
and startdate like @startdate_temp
open cpc3_cur
fetch next from cpc3_cur into @cpc3code
while @@fetch_status = 0
begin
--select @cpc3code as 'cpc1code',@consultantno as 'consultantno'
select @total_temp=total from cpc3_view where cpc3code=@cpc3code and consultantno=@consultantno
set @i = @i + 1
set @total = @total + @total_temp
--select @i as 'i',@total_temp as 'total_temp',@total as 'total'
fetch next from cpc3_cur into @cpc3code
end
close cpc3_cur
deallocate cpc3_cur
if @i = 0
set @total = 0
else
set @total = cast((@total/@i) as int)
select @total as 'ave'end
把你原来使用declare @total去掉就要以了使用了。(datatype 为 int)
请高手进 http://www.csdn.net/expert/topic/674/674934.xml?temp=9.028262E-02 来帮帮小弟的忙!!
as
begin
declare @cpc3code char(8)
declare @startdate_temp char(8)
declare @total_temp int
declare @total int
declare @i int
set @startdate_temp = @startdate + '%'
set @i = 0
set @total_temp = 0
set @total = 0
--select @consultantno as 'consultantno',@startdate as 'startdate'
declare cpc3_cur cursor for select cpc3code from cpc3_view where consultantno like @consultantno
and startdate like @startdate_temp
open cpc3_cur
fetch next from cpc3_cur into @cpc3code
while @@fetch_status = 0
begin
--select @cpc3code as 'cpc1code',@consultantno as 'consultantno'
select @total_temp=total from cpc3_view where cpc3code=@cpc3code and consultantno=@consultantno
set @i = @i + 1
set @total = @total + @total_temp
--select @i as 'i',@total_temp as 'total_temp',@total as 'total'
fetch next from cpc3_cur into @cpc3code
end
close cpc3_cur
deallocate cpc3_cur
if @i = 0
set @total = 0
else
set @total = cast((@total/@i) as int)
--select @total as 'ave'
return @total
end
go