oracle函数转换成sqlserver函数

[coralandbill]

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;



最好带解释 谢谢大神了！

[Leon_He2014]

楼主好懒啊，能不能把问题问得细一点

[wangchangming]

create function fn_PayPrepayBillItem(@swhere nvarchar(max),@sorderby nvarchar(max)) returns table as begin ...... end

[铁歌]

create function fn_PayPrepayBillItem(@swhere nvarchar(max),@sorderby nvarchar(max)) returns table as begin DECLARE @stype varchar2(20) DECLARE @like_cur table(@swhere nvarchar(max),@sorderby nvarchar(max)) DECLARE @stempsql varchar2(10000) DECLARE @stempwhere varchar(10000) --oracle into change into select @var --if ..else..begin ...end的修改 select @stype =fitemvaluen from jczl_sysinfo where sitemname='SFDPaytype'; --根据采购订单 if '1'=stype begin if @stempwhere is null begin @stempwhere =' where m.iPayType=2 and p.iBillLinkType=1 and p.iBillType=1'; end else begin @stempwhere=stempwhere + ' and m.iPayType=2 and p.iBillLinkType=1 and p.iBillType=1'; end; --连接符|| 改成'+' --nvl changed into isnull 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,isnll(p.iBillLinkType,1),isnll(p.iIsCheck,0), isnll(p.fWeight,0) as iWeight,--重量 isnll(p.fPrice,0)as iInPrice,--价格 isnll(p.fBillAmount,0)as iMoney,--金额 isnll(cw.fAmount,0)as fAmount, --已核销金额 (isnll(p.fBillAmount,0)-isnll(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; --oracle使用游标打开SQL，而MSSQL使用临时表或表变量，可以使用存储过程直接插入即可格式如下，中间的列没有填写.. insert into @like_cur(...) exec(@stempsql;) end ; if '2'=stype begin --根据入库订单 if stempwhere is null begin stempwhere=' where m.iPayType=2 and p.iBillLinkType=1 and p.iBillType=1'; end else begin stempwhere=stempwhere + ' and m.iPayType=2 and p.iBillLinkType=1 and p.iBillType=1'; end ; 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,isnll(p.iBillLinkType,2),isnll(p.iIsCheck,0), isnll(p.fWeight,0) as iWeight, --重量 isnll(p.fPrice,0) as iInPrice, --价格 isnll(p.fBillAmount,0) as iMoney, --金额 isnll(cw.fAmount,0) as fAmount, --已核销金额 (isnll(p.fBillAmount,0)-isnll(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; --oracle使用游标打开SQL，而MSSQL使用临时表或表变量，可以使用存储过程直接插入即可格式如下，中间的列没有填写.. insert into @like_cur(...) exec(@stempsql;) end; end

[coralandbill]

谢谢大家啦 自己搞定了辛苦亲们了 结贴啦