这是存储过程，它已经用自己的语法运行sql server 2005，在这里我用mysql语法写了这个。一切正常，但最后结束语句显示错误。
分隔符 $$
创建定义器=root@localhost过程sp_PREPAREACCOUNTREPORTSBEGIN(在fdate1日期、tdate1日期、GrossProfit双精度、NetProfit双精度、OpeningStock双精度、ClosingStock双精度、DiffInBalance双精度、dcVARCHAR(25)
)开始
Create temporary table if not exists Trading
(Ac_code bigint, AcName varchar(255),Amount float,DC varchar(20),seq int) ENGINE=MEMORY;
insert into Trading SELECT ledger_master.Ac_code, ledger_master.Ac_name,
(select case tmp_closingbalance.Dr when 0 then tmp_closingbalance.Cr else tmp_closingbalance.Dr end), (select case tmp_closingbalance.Dr when 0 then 'cr' else 'dr' end),sequence
FROM ledger_master INNER JOIN tmp_closingbalance ON ledger_master.Ac_code = tmp_closingbalance.ac_code INNER JOIN accountgroup ON ledger_master.Group_code = accountgroup.Group_code
where accountgroup.position='t' order by sequence;
set @GrossProfit := 0;
set @GrossProfit := (select ifnull(sum(Amount),0) from Trading where DC='dr') - (select ifnull(sum(Amount),0) from Trading where DC='cr');
if(@GrossProfit >0)
THEN
insert into Trading values(0,'GROSS PROFIT', (@GrossProfit),'cr',9999 );
else if(@GrossProfit <0)
then
insert into Trading values(0,'GROSS PROFIT', -(@GrossProfit),'dr',9999 );
end if;
Create temporary table if not exists PL
(Ac_code bigint,AcName varchar(255),Amount float,DC varchar(20),seq int) ENGINE=MEMORY;
insert into PL
SELECT ledger_master.Ac_code, ledger_master.Ac_name,
(select case tmp_closingbalance.Dr when 0 then tmp_closingbalance.Cr else tmp_closingbalance.Dr end),
(select case tmp_closingbalance.Dr when 0 then 'cr' else 'dr' end),sequence
FROM ledger_master INNER JOIN
tmp_closingbalance ON ledger_master.Ac_code = tmp_closingbalance.ac_code INNER JOIN
accountgroup ON ledger_master.Group_code = accountgroup.Group_code
where accountgroup.position='p' order by sequence;
if (@GrossProfit >0)
then
insert into PL values(0,'BY GROSS PROFIT', (@GrossProfit),'dr',9999 );
else if (@GrossProfit <0)
then
insert into PL values(0,' BY GROSS PROFIT', -(@GrossProfit),'cr',9999 );
end if;
set @NetProfit := 0;
set @NetProfit := (select ifnull(sum(Amount),0) from PL where DC='dr') - (select ifnull(sum(Amount),0) from PL where DC='cr');
if(@NetProfit >0)
then
insert into PL values(0,'TO NET PROFIT', (@NetProfit),'cr',9999 );
else if(@NetProfit <0)
then
insert into PL values(0,'TO NET PROFIT', -(@NetProfit),'dr',9999 );
end if;
Create temporary table if not exists Balancesheet
(Ac_code bigint,AcName varchar(255),Amount float,DC varchar(20),seq int) ENGINE=MEMORY;
insert into Balancesheet
SELECT ledger_master.Ac_code,ledger_master.Ac_name,
(select case tmp_closingbalance.Dr when 0 then tmp_closingbalance.Cr else tmp_closingbalance.Dr end), (select case tmp_closingbalance.Dr when 0 then 'cr' else 'dr' end),sequence
FROM ledger_master INNER JOIN tmp_closingbalance ON ledger_master.Ac_code = tmp_closingbalance.ac_code INNER JOIN accountgroup ON ledger_master.Group_code = accountgroup.Group_code
where accountgroup.position='b' order by sequence;
insert into Balancesheet values(0,'PROFIT & LOSS A/C',-(@NetProfit),'cr',9999);
set @DiffInBalance := 0;
select @DiffInBalance := ((select ifnull(sum(Amount),0) from Balancesheet where DC='dr') - ( select ifnull(sum(Amount),0) from Balancesheet where DC='cr'));
delete from tmp_prepareaccountreports;
insert into tmp_prepareaccountreports select Ac_code,AcName,Amount,DC,seq,1 as types from Trading order by types,seq;
insert into tmp_prepareaccountreports select Ac_code,AcName,Amount,DC,seq,2 as types from PL order by types,seq;
insert into tmp_prepareaccountreports select Ac_code,AcName,Amount,DC,seq,3 as types from Balancesheet order by types,seq;
完$$ 定界符;
ELSE如果不是ELSE，如果请查看 https://dev.mysql.com/doc/refman/8.0/en/if.html