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;
最好带解释 谢谢大神了！ Oracle SQL?Server
[解决办法]
楼主好懒啊，能不能把问题问得细一点
[解决办法]
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
