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