oracle函数转换成sqlserver函数

create or replace function fn_PayPrepayBillItem(swhere varchar2,sorderby varchar2)
return sys_refcursor
is
stype varchar2(20);
like_cur sys_refcursor;
stempsql varchar2(10000);
stempwhere varchar(10000):=swhere;
begin
select fitemvaluen into stype from jczl_sysinfo where sitemname='SFDPaytype';
--根据采购订单
if '1'=stype then
if stempwhere is null then
stempwhere:=' where m.iPayType=2 and p.iBillLinkType=1 and p.iBillType=1';
else
stempwhere:=stempwhere || ' and m.iPayType=2 and p.iBillLinkType=1 and p.iBillType=1';
end if;
stempsql:='
select p.sguid,(m.sbillno ||''---''|| p.sSubCode)as spayno,m.dBillDate,h.scorpname as skcorpname,
sm.srealname,d.sdeptname,g.sgaincentername,
case p.iBillLinkType when 1 then ''采购订单'' when 2 then ''入库单'' when 3 then ''销售订单'' when 4 then ''提单''
when 5 then ''出库单'' when 6 then ''费用'' end as linktypename,
case p.iBillType when 1 then ''货费'' when 2 then ''费用'' when 3 then ''其他'' end as billtypename,
case p.iIsCheck when 0 then ''否'' when 1 then ''是'' end scheckname,
gt.scatname,cg.sgoodsname,cg.sspec,cg.smaterial,cg.sparea,cg.squality,
m.sFinanceCenter,nvl(p.iBillLinkType,1),nvl(p.iIsCheck,0),
nvl(p.fWeight,0) as iWeight,--重量
nvl(p.fPrice,0)as iInPrice,--价格
nvl(p.fBillAmount,0)as iMoney,--金额
nvl(cw.fAmount,0)as fAmount, --已核销金额
(nvl(p.fBillAmount,0)-nvl(cw.fAmount,0))as funamount --未核销金额
from SFD_PrepayBillItem p
left join SFD_PrepayBillMain m on m.sguid=p.sBillid
left join hy_corp h on m.sBillCorp=h.scorpid
left join sm_operator sm on m.sSales=sm.soperator
left join jczl_department d on m.sDeptid=d.sdepid
left join JCZL_GainCenter g on m.sFinanceCenter=g.sgaincenterid
left join CG_PODItem cg on p.sBillLinkCode=cg.sguid
left join jczl_goods gs on cg.sgoodsname=gs.sgoodsname
left join jczl_goodscat gt on gs.scategoryid=gt.scategory
left join cw_IncomeOrPayPrepay cw on cw.sBillid=p.sguid' || stempwhere || sorderby;
-- insert into outff( afe ) values(stempsql);
open like_cur for stempsql ;
return like_cur;
end if;
if '2'=stype then
--根据入库订单
if stempwhere is null then
stempwhere:=' where m.iPayType=2 and p.iBillLinkType=1 and p.iBillType=1';
else
stempwhere:=stempwhere || ' and m.iPayType=2 and p.iBillLinkType=1 and p.iBillType=1';
end if;
stempsql:='
select p.sguid,(m.sbillno ||''---''|| p.sSubCode)as spayno,m.dBillDate,h.scorpname as skcorpname,
sm.srealname,d.sdeptname,g.sgaincentername,p.iIsCheck,
case p.iBillLinkType when 1 then ''采购订单'' when 2 then ''入库单'' when 3 then ''销售订单'' when 4 then ''提单''
when 5 then ''出库单'' when 6 then ''费用'' end as linktypename,
case p.iBillType when 1 then ''货费'' when 2 then ''费用'' when 3 then ''其他'' end as billtypename,
case p.iIsCheck when 0 then ''否'' when 1 then ''是'' end scheckname,
gt.scatname,rk.sgoodsname,rk.sspec,rk.smaterial,rk.spdarea as sparea,rk.squality,
m.sFinanceCenter,nvl(p.iBillLinkType,2),nvl(p.iIsCheck,0),
nvl(p.fWeight,0) as iWeight, --重量
nvl(p.fPrice,0) as iInPrice, --价格
nvl(p.fBillAmount,0) as iMoney, --金额
nvl(cw.fAmount,0) as fAmount, --已核销金额
(nvl(p.fBillAmount,0)-nvl(cw.fAmount,0))as funamount --未核销金额
from SFD_PrepayBillItem p
left join SFD_PrepayBillMain m on m.sguid=p.sBillid
left join hy_corp h on m.sBillCorp=h.scorpid
left join sm_operator sm on m.sSales=sm.soperator
left join jczl_department d on m.sDeptid=d.sdepid
left join JCZL_GainCenter g on m.sFinanceCenter=g.sgaincenterid
left join CG_receive rk on p.sBillLinkCode=rk.sguid
left join jczl_goods gs on rk.sgoodsname=gs.sgoodsname
left join jczl_goodscat gt on gs.scategoryid=gt.scategory
left join cw_IncomeOrPayPrepay cw on cw.sBillid=p.sguid'|| stempwhere || sorderby;
--insert into outff( afe )values(stempsql);
open like_cur for stempsql;
return like_cur;
end if;
end;



最好带解释 谢谢大神了！