SQLserver用游标实现循环 和 简单的量值函数

declare @sums numeric(15,2)    -- 这里必须要写上位数，不然默认为整数
declare @cltno varchar(32) 
declare @feeMoney numeric(15,2)
declare cur cursor for sELECT  CltNo FROM  memberinfo WHERE CltNo NOT IN ('00029098','00027565')   -- 定义游标 
select @sums = 0.0 
open cur                                                                            --打开游标
fetch next from cur  into @cltno
while @@fetch_status = 0 
begin
  SELECT @feeMoney = fee 
        FROM Ns_CMS_Gettable(@cltno,
                                    '2015-10-01',
                                    '22'); 
select  @sums = @sums + @feeMoney;
fetch next  from cur into @cltno
end 
close cur                                                             -- 关闭游标
Deallocate cur                                                        -- 删除游标
print @sums                                                         -- 输出
print cast(@sums as varchar(10)) + 'ddddddd'     -- 把数字类型转换成字符串

 

函数

create FUNCTION [dbo].[ns_cms_getnewfeemoney](@cid int) 
returns numeric(15,2) 
as 
begin
    declare @sums numeric(15,2) 
    declare @cltno varchar(32) 
    declare @feeMoney numeric(15,2)
    declare cur cursor for sELECT  CltNo FROM  memberinfo WHERE CltNo NOT IN ('00029098','00027565')   -- 定义游标 
    begin
    select @sums = 0.0 
    open cur
    fetch next from cur  into @cltno
    while @@fetch_status = 0 
    begin
      SELECT @feeMoney = fee 
            FROM Ns_CMS_Gettable(@cltno,
                                        '2015-10-01',
                                        '22'); 
    select  @sums = @sums + @feeMoney;
    fetch next  from cur into @cltno
    end 
    close cur                                                             -- 关闭游标
    Deallocate cur                                                        -- 删除游标
  end 
       RETURN @sums 
end