sqlserver运用了值函数和游标结合
--欠款实收的函数 ALTER FUNCTION [dbo].[getAllottedDate](@assetsid varchar(32),@type varchar(32)) RETURNS varchar(512) AS BEGIN DECLARE @result varchar(512) DECLARE @debtMoney float DECLARE @debtDate varchar(32) DECLARE @recoveriesMoney float declare @temp varchar(50) if(@type='shishou') begin select @debtMoney=isnull(sum(field007),0)*(-1) from ufv2m7k41656039640871 where requestid in(select id from workflowbase where isdelete=0 and isfinished=1) and field007<0 and field001=@assetsid select @recoveriesMoney=isnull(sum(field007),0) from ufv2m7k41656039640871 where requestid in(select id from workflowbase where isdelete=0 and isfinished=1) and field007>0 and field001=@assetsid if(@recoveriesMoney>=@debtMoney)------还清 begin return @result end else----未还清,循环比较月份欠款额 begin declare mycursor cursor fast_forward for select (field009+'-'+field010) as debtDate,sum(field007) as debtMoney from ufv2m7k41656039640871 where requestid in(select id from workflowbase where isdelete=0 and isfinished=1) and field001=@assetsid and field007<0 group by field009,field010 order by (field009+field010) asc --打开游标 open mycursor --从游标里取出数据赋值到我们刚才声明的2个变量中 fetch next from mycursor into @debtDate,@debtMoney --判断游标的状态 -- 0 fetch语句成功 ---1 fetch语句失败或此行不在结果集中 ---2 被提取的行不存在 while (@@fetch_status=0) begin --显示出我们每次用游标取出的值 set @recoveriesMoney=@debtMoney+@recoveriesMoney if(@recoveriesMoney<0) begin if(@result is null) begin set @result=@debtDate+',' end else begin set @result=@result+@debtDate+',' end end --用游标去取下一条记录 fetch next from mycursor into @debtDate,@debtMoney end close mycursor --关闭游标 deallocate mycursor --释放游标 end end RETURN @result END