SQL 嵌套游标的使用

CREATE FUNCTION fGetsupmaterial(@startDate DateTime,@EndDate DateTime,@chejianid int)
RETURNS @Supmaterial TABLE (supamount varchar(50), supname varchar(50),proid int)
AS
BEGIN
declare @name varchar(50)
declare @sumname varchar(50)
declare @amount varchar(50)
declare @sumamount varchar(50)
declare @proid int
declare @proid1 int
declare @flag int
set @flag=0

 --  insert into @Supmaterial(supamount,supname,proid) values(0,0,0)

declare my_cursor cursor for

select sum(sup.SupM_amount) as amount,SM.SupMaterialname,sup.proid from XJ_InputSupM sup,XJ_supMaterial SM
where sup.SupMatID=SM.SupMaterialID and sup.chejianid=@chejianid  and sup.SupmatDate>=@startDate and
sup.SupmatDate<=@EndDate group by SM.SupMaterialname,sup.proid

open my_cursor

fetch next from my_cursor into @amount,@name,@proid
--set @sumamount=@amount+'/'
--set @sumname=@name+'/'
--insert into @Supmaterial(supamount,supname,proid) values(@sumamount,@sumname,@proid)--***************
--insert into @Supmaterial(supamount,supname,proid) values(80,123,4)
while @@FETCH_STATUS=0
begin
--------------------------------------------------------------------------
   declare my_cursor1 cursor for
   select * from @Supmaterial
   open my_cursor1
   fetch next from my_cursor1 into @sumamount,@sumname,@proid1
--   if @proid=@proid1
--     begin
--    set @sumname=@sumname+@name+'/'
--    set @sumamount=@sumamount+@amount+'/'
-- update @Supmaterial set supamount=@sumamount,supname=@sumname where proid=@proid1--**************
      --insert into @Supmaterial(supamount,supname,proid) values(@amount,@name,@proid)
--     end
--   else
--     begin
------------------------------------------------------
 while @@FETCH_STATUS=0
   begin
      if @proid=@proid1
         begin
        set @sumname=@sumname+@name+'/'
        set @sumamount=@sumamount+@amount+'/'
     update @Supmaterial set supamount=@sumamount,supname=@sumname where proid=@proid1--*********************
   -- insert into @Supmaterial(supamount,supname,proid) values(@amount,@name,@proid)
            Set @flag=1
         end
      fetch next from my_cursor1 into @sumamount,@sumname,@proid1
   end
-----------------------------------------------------------
--   end
   close my_cursor1
   deallocate my_cursor1
   if @flag=0
     begin
        set @amount=@amount+'/'
        set @name=@name+'/'
        insert into @Supmaterial(supamount,supname,proid) values(@amount,@name,@proid)--************************
     end
   set @flag=0
fetch next from my_cursor into @amount,@name,@proid
end
close  my_cursor
deallocate my_cursor
--select @sumname,@sumamount
--insert into @Supmaterial(supname,supamount) values(@sumname,@sumAmount)
return
end 

本人写的第一个也是甚有成就感的一段使用了嵌套游标的SQL函数。