sql server里函數的兩種用法（可以代替游標）
1． 因為update里不能用存儲過程，然而要根據更新表的某些字段還要進行計算。我們常常采用游標的方法，這里用函數的方法實現。

函數部分：
create function [dbo].[fun_gettime] (@taskphaseid int)
returns float as
begin
  declare @taskid int,
          @hour float,
          @percent float,
          @return float
  if @taskphaseid is null
  begin
    return(0.0)
  end

select @taskid=taskid,@percent=isnull(workpercent,0)/100
from tabletaskphase
where [email protected]

select @hour=isnull(tasktime,0) from tabletask
where [email protected]

set @[email protected]*@percent
return (@return)
end

調用函數的存儲過程部分
create procedure [dbo].[proc_calcca]
@roid int
  as
begin
  declare @ca float

  update tablefmeca
  set
  cvalue_m=    isnull(moderate,0)*isnull(fmerate,0)*isnull(b.basfailurerate,0)*[dbo].[fun_gettime](c.id)
from tablefmeca ,tablerelation b,tabletaskphase c
where [email protected] and taskphaseid=c.id and [email protected]

  select @ca=sum(isnull(cvalue_m,0)) from tablefmeca where [email protected]

update tablerelation
  set [email protected]
  where [email protected]
end
go

2． 我們要根據某表的某些記錄，先計算后求和，因為無法存儲中間值，平時我們也用游標的方法進行計算。但sqlserver2000里支持
sum ( [ all | distinct ] expression )

expression

是常量、列或函數，或者是算術、按位與字符串等運算符的任意組合。因此我們可以利用這一功能。

函數部分：

create function [dbo].[fun_rate] (@partid int,@enid int,@sourceid int, @qualityid int,@count int)

returns float as
begin
  declare @qxs float, @g float, @rate float

  if (@enid=null) or (@partid=null) or (@sourceid=null) or (@qualityid=null)
  begin
    return(0.0)
  end

  select @qxs= isnull(xs,0) from tablequality where [email protected]
  select @g=isnull(frate_g,0) from tablefailurerate
  where ([email protected]) and( [email protected]) and ( [email protected]) and( ( (isnull(mincount,0)<=isnull(@count,0)) and ( isnull(maxcount,0)>=isnull(@count,0)))
or(isnull(@count,0)>isnull(maxcount,0)))

  set @rate=isnull(@qxs*@g,0)
  return (@rate)
end

調用函數的存儲過程部分：

create proc proc_faultrate

@partid integer, @qualityid integer, @sourceid integer, @count integer, @roid int, @grade int,@rate float=0 outputas
begin
  declare
    @taskid int
    set @rate=0.0

select @taskid=isnull(taskproid,-1) from tablerelation where id=(select pid from tablerelation where [email protected])

    if (@taskid=-1) or(@grade=1) begin
       set @rate=0
    return
end

    select @rate=sum([dbo].[fun_rate] (@partid,enid,@sourceid, @qualityid,@count) *isnull(workpercent,0)/100.0)

    from tabletaskphase
    where [email protected]
end
go

函數還可以返回表等，希望大家一起討論sqlserver里函數的妙用。