日期:2014-05-17  浏览次数:20431 次

存数过程转函数

@endtime datetime

 AS

   declare @tempTable  table(businessId int,businessname varchar(50),subbusinessid int,business_subname varchar(50))

  insert into @tempTable (businessid,businessname,subbusinessid,business_subname)
  select b.businessid,b.business_name,sub.subbusinessid,sub.business_subname
  from business_typemain b,business_type sub
  where b.businessid=sub.businessid

  insert into @tempTable (businessid,businessname,subbusinessid,business_subname)
  select businessid,business_name,0,' '
  from business_typemain 
  where businessid not in (select businessid from business_type)

  declare business cursor for
        select businessid,subBusinessid,businessname,business_subname from @tempTable
   open business

   declare @tempNumber  table(business_name varchar(50),business_subname varchar(50),n int)

   while 1 = 1
    begin
        declare @bid int
        declare @subid int
        declare @bname varchar(50)
        declare @subname varchar(50)

        fetch next from business into @bid,@subid,@bname,@subname
        if @@fetch_status <> 0
            break

       insert into @tempNumber(business_name,business_subname,n)
       values(@bname,@subname,0)

       insert into @tempNumber(business_name,business_subname,n)
       select @bname,@subname,1
       from archive
       where jointime <=@endtime and jointime>=@starttime and CAST(SUBSTRING(registerid, 1, 2) AS int)=@bid and CAST(SUBSTRING(registerid, 3, 2) AS int)=@subid

    end
  
 close business
 deallocate business

 select business_name,sum(n) as num
 from @tempNumber
 group by  business_name






GO




如题:常规的函数我会写,这个我没有思路了。

望高人帮忙指点
------最佳解决方案--------------------
create function f_tempNumber
(@starttime datetime,
@endtime datetime
)
returns @tempNumber  table(business_name varchar(50),business_subname varchar(50),n int)
begin
declare @tempTable  table(businessId int,businessname varchar(50),subbusinessid int,business_subname varchar(50))

  insert into @tempTable (businessid,businessname,subbusinessid,business_subname)
  select b.businessid,b.business_name,sub.subbusi