我有個專案DELPHI前臺開發的呼叫一個存貯程序，執行的結果與直接在查詢分析器里執行的結果不同，發現有一段代碼不起做用
用查詢分析器執行的結果是對的，程式里不連續的有部份代碼沒有執行到，很奇怪，下面是前臺代碼及后面存貯程序代碼，我分三次上傳，請高手幫我看看，感謝
前臺代碼：
var
adoquery:tadoquery;
begin
try
form1.ADOConnection1.BeginTrans ;
adoquery:=tadoquery.Create(nil);
adoquery.Connection:=form1.ADOConnection1;
adoquery.Close;
adoquery.SQL.Clear;
adoquery.SQL.Add('exec MyBOMExpand_hgnew141228 '''+formatdatetime('yyyy-MM-dd',datetimepicker1.DateTime)+''','''+formatdatetime('yyyy-MM-dd',datetimepicker2.DateTime)+''','''+formatdatetime('yyyy-MM-dd',datetimepicker3.DateTime)+''', '''+formatdatetime('yyyy-MM-dd',datetimepicker4.DateTime)+'''');
adoquery.ExecSQL;
adoquery.Free;
form1.ADOConnection1.CommitTrans;
showmessage('生成成功！');
except
form1.ADOConnection1.RollbackTrans;
showmessage('生成夫敗！');
end;
end;
uj5u.com熱心網友回復：
===================================================================================
存貯程序代碼：
ALTER proc [dbo].[MyBOMExpand_hgnew141228]
@startdate datetime,
@enddate datetime,
@startBackupdate datetime,
@endbackupdate datetime
as
/************************************************
創建臨時表
MyBOMExpand_hgnew141228 '2014-11-1','2014-11-30','2014-11-21','2014-12-10'
select * from ##qty where fnumber='TM-ERZ-0Y60000760-1'
************************************************/
If Object_Id( 'Tempdb..#myallexpand ') Is Not NULL
drop table #myallexpand
If Object_Id( 'Tempdb..#myallexpand_mx ') Is Not NULL
drop table #myallexpand_mx
--If Object_Id( 'Tempdb..##qty') Is Not NULL
--drop table ##qty
If Object_Id( 'Tempdb..#qty') Is Not NULL
drop table #qty
declare @month int--定義月份
declare @year int --定義年份
declare @monthint int --定義上月 可用月份
declare @yearint int --定義上月可用年份
set @month=DATEPART(mm,@startdate)
set @year=DATEPART(yy,@startdate)
if(@month=12)
begin
set @monthint=1
set @yearint=@year+1
end
else
begin
set @monthint=@month+1
set @yearint=@year
end
Create Table #myallexpand
(
Fitemid int,
Fnumber nvarchar(50),
SYKY decimal(20,4) default(0),--上月可使用A
BYSL decimal(20,4) default(0),--本月上料B
BYCH decimal(20,4) default(0),--本月出貨C
QTCK decimal(20,4) default(0),--本月其他出庫
BYTL decimal(20,4) default(0),--本月退料D
LLJC as (SYKY+BYSL-BYCH+BYTL-QTCK),--本月理論結存E=A+B-C-D
bpLPHJ decimal(20,4) default(0),--部品良品合計F1
bpSCBL decimal(20,4) default(0),--部品生產不良F2
LLBL decimal(20,4) default(0),--來料不良F3
cpLPHJ decimal(20,4) default(0),--成品良品合計G1
cpSCBL decimal(20,4) default(0),--成品生產不良G2
bcpLPHJ decimal(20,4) default(0),--半成品良品合計H1
bcpSCBL decimal(20,4) default(0),--半成品生產不良 H2
KHZC decimal(20,4) default(0),--客戶資產K
PDHJ as bpLPHJ+bpSCBL+LLBL+ cpLPHJ+cpSCBL+bcpLPHJ+bcpSCBL+KHZC--本月盤點合計L=F1G+H+I+J+K
)
Create Table #myallexpand_mx
(
fnumber_p varchar(255),
fqty_p decimal(20,4) default(0),
ftypeid varchar(100),
Fitemid int,
Fnumber nvarchar(50),
fqty_yl decimal(20,4) default(0),--用量
SYKY decimal(20,4) default(0),--上月可使用A
BYSL decimal(20,4) default(0),--本月上料B
BYCH decimal(20,4) default(0),--本月出貨C
QTCK decimal(20,4) default(0),--本月其他出庫
BYTL decimal(20,4) default(0),--本月退料D
-- LLJC as (SYKY+BYSL-BYCH+BYTL-QTCK),--本月理論結存E=A+B-C-D
bpLPHJ decimal(20,4) default(0),--部品良品合計F1
bpSCBL decimal(20,4) default(0),--部品生產不良F2
LLBL decimal(20,4) default(0),--來料不良F3
cpLPHJ decimal(20,4) default(0),--成品良品合計G1
cpSCBL decimal(20,4) default(0),--成品生產不良G2
bcpLPHJ decimal(20,4) default(0),--半成品良品合計H1
bcpSCBL decimal(20,4) default(0),--半成品生產不良 H2
KHZC decimal(20,4) default(0),--客戶資產K
-- PDHJ as bpLPHJ+bpSCBL+LLBL+ cpLPHJ+cpSCBL+bcpLPHJ+bcpSCBL+KHZC--本月盤點合計L=F1G+H+I+J+K
)
Create Table #Qty
(
Fitemid int,
Fnumber nvarchar(255),
fqty decimal(20,4) default(0),
ftypeid varchar(255)
)
--drop table wanghx_qty
-- Create Table wanghx_Qty
-- (
-- fyear int,
-- fmonth int,
-- fnumber_p varchar(255),
-- fqty_p decimal(20,4) default(0),
-- Fitemid int,
-- fqty_yl decimal(20,4) default(0),
-- fqty decimal(20,4) default(0),
-- ftypeid varchar(255)
-- )
delete wanghx_qty where fyear=@year and fmonth=@month
/**************************理論盤點資料******************************/
--================上月可用====================
insert into #myallexpand_mx (Fitemid,SYKY) --上月可用 A
Select t3.fitemid,t3.PDHJ
From PDHJ t3
Where t3.yearint=@year and t3.monthint =@month and cast(pdhj as decimal(20,4))>0
insert into wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,Fitemid,fqty_yl,fqty,ftypeid)
Select @year,@month,'部品',null,t3.fitemid,0,t3.PDHJ,'上月可用'
From PDHJ t3,t_icitem t4
Where t3.fitemid=t4.fitemid and t3.yearint=@year and t3.monthint =@month and cast(t3.pdhj as decimal(20,4))>0
-- =======本月上料===================
insert into #myallexpand_mx (Fitemid,BYSL) --本月上料 B
Select t3.fitemid,SUM(t2.Fauxqty)
From vwICBill_1 t2,t_icitem t3
Where t2.FFullNumber=t3.Fnumber and (t2.Fdate between @startdate and @enddate) and t2.fauxqty>0
GROUP By t3.FItemID
insert into wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,Fitemid,fqty_yl,fqty,ftypeid)
Select @year,@month,'部品',null,t3.fitemid,null,SUM(t2.Fauxqty),'本月上料'
From vwICBill_1 t2,t_icitem t3
Where t2.FFullNumber=t3.Fnumber and t2.Fdate between @startdate and @enddate and t2.fauxqty>0
GROUP By t3.FItemID
-- =======本月退料===================
insert into #myallexpand_mx (Fitemid,BYTL)--本月退料 D
Select t3.fitemid,SUM(t2.Fauxqty)
From vwICBill_1 t2,t_icitem t3
Where t2.FFullNumber=t3.Fnumber and t2.Fdate between @startdate and @enddate and t2.fauxqty<0
GROUP By t3.FItemID
insert into wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,Fitemid,fqty_yl,fqty,ftypeid)--本月退料 D
Select @year,@month,'部品',null,t3.fitemid,0,SUM(t2.Fauxqty),'本月退料'
From vwICBill_1 t2,t_icitem t3
Where t2.FFullNumber=t3.Fnumber and t2.Fdate between @startdate and @enddate and t2.fauxqty<0
GROUP By t3.FItemID
--=======本月出貨===================
insert into #Qty (Fitemid,t3.fnumber,fqty,ftypeid)--本月出貨bych C
Select t3.fitemid,t3.fnumber,SUM(t2.Fauxqty),'本月出貨'
From vwICBill_8 t2,t_icitem t3
Where t2.FFullNumber=t3.Fnumber and t2.Fdate between @startdate and @enddate
GROUP By t3.FItemID,t3.fnumber
--vwICBill_1外購入庫對1，vwICBill_11領料單24 vwICBill_8銷售出庫 對21 ,vvicbill_16其它出庫單 29
--=================貿易出貨====================
insert into #Qty (Fitemid,fnumber,fqty,ftypeid) --本月出貨備注貿易出貨 BYCH
select b.fitemid,b.fnumber,a.fauxqty,'貿易出貨'
from vwICBill_11 a,t_icitem b
where a.fitemidname=b.fnumber and a.fdate between @startdate and @enddate and a.FHeadSelfB0434 like '%貿易出貨%'
and (b.fnumber like 'TM-ES%' or b.fnumber like 'TM-EF%') and a.fauxqty<>0
If Object_Id( 'Tempdb..#t1') Is Not NULL
drop table #t1
select identity(int,1,1) as 序號 , t1.* into #t1 from #Qty t1 where ftypeid in ('本月出貨','貿易出貨')
declare @n int
declare @fqty_b decimal(20,4)
declare @fnumber_b varchar(255)
set @n=null
set @fqty_b=null
set @fnumber_b=null
set @n=1
while @n<=(select max(序號) from #t1)
begin
select @fnumber_b=fnumber,@fqty_b=fqty from #t1 where 序號=@n
insert #myallexpand_mx(fnumber_p,fqty_p,fitemid,fqty_yl,bych,ftypeid)
exec aaa_BomExpand_hg @fnumber_b ,50,1,'1900-01-01 00:00:00','2100-01-01 00:00:00',@fqty_b,'本月出貨和貿易出貨'
set @n=@n+1
end
insert wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,fitemid,fqty_yl,fqty,ftypeid)
select @year,@month,fnumber_p,fqty_p,fitemid,fqty_yl,bych,ftypeid from #myallexpand_mx where ftypeid='本月出貨和貿易出貨'
--=================附件=============
insert into #myallexpand_mx (Fitemid,bych)--本月出貨取領料單生產拉線 C
select b.fitemid,a.fauxqty
from vwICBill_11 a,t_icitem b
where a.fitemidname=b.fnumber and a.fdate between @startdate and @enddate and a.FHeadSelfB0434 like '%附料%'
insert into wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,Fitemid,fqty_yl,fqty,ftypeid)--本月出貨取領料單生產拉線 C
select @year,@month,'部品',null,b.fitemid,0,a.fauxqty,'附料'
from vwICBill_11 a,t_icitem b
where a.fitemidname=b.fnumber and a.fdate between @startdate and @enddate and a.FHeadSelfB0434 like '%附料%'
---==================其它出庫是部品的==============
insert into #myallexpand_mx (Fitemid,QTCK)--本月其他出庫部品
SELECT b.fitemid,a.fauxqty
from vwICBill_16 a,t_icitem b
where a.fitemidname=b.fnumber and a.fdate between @startdate and @enddate
and b.fnumber not like 'TM-ES%' and b.fnumber not like 'TM-EF%'
insert into wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,Fitemid,fqty_yl,fqty,ftypeid)--本月出貨取領料單生產拉線 C
SELECT @year,@month,'部品',null,b.fitemid,0,a.fauxqty,'其他出庫部品'
from vwICBill_16 a,t_icitem b
where a.fitemidname=b.fnumber and a.fdate between @startdate and @enddate
and b.fnumber not like 'TM-ES%' and b.fnumber not like 'TM-EF%'
---==================其它出庫是成品的==============
insert into #Qty (Fitemid,fnumber,fqty,ftypeid) --本月出其他出庫成品
select b.fitemid,b.fnumber,a.fauxqty,'出其他出庫成品'
from vwICBill_16 a,t_icitem b
where a.fitemidname=b.fnumber and a.fdate between @startdate and @enddate
and (b.fnumber like 'TM-ES%' or b.fnumber like 'TM-EF%')
If Object_Id( 'Tempdb..#t2') Is Not NULL
drop table #t2
select identity(int,1,1) as 序號 , t1.* into #t2 from #Qty t1 where ftypeid in ('出其他出庫成品')
set @n=null
set @fqty_b=null
set @fnumber_b=null
set @n=1
while @n<=(select max(序號) from #t2)
begin
select @fnumber_b=fnumber,@fqty_b=fqty from #t2 where 序號=@n
insert #myallexpand_mx (fnumber_p,fqty_p,fitemid,fqty_yl,bych,ftypeid)
exec aaa_BomExpand_hg @fnumber_b ,50,1,'1900-01-01 00:00:00','2100-01-01 00:00:00',@fqty_b,'出其他出庫成品'
set @n=@n+1
END
insert wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,fitemid,fqty_yl,fqty,ftypeid)
select @year,@month,fnumber_p,fqty_p,fitemid,fqty_yl,bych,ftypeid from #myallexpand_mx where ftypeid='出其他出庫成品'
uj5u.com熱心網友回復：
--***************************************實際盤點資料****************************************************************--**********************************************
--部品F1F2F3
--*******************************************
--==================來料不良=====================
insert into #myallexpand_mx (Fitemid,LLBL)--來料不良F1
select i.fitemid,i.fqtyact from icinvbackup i
join t_stock t
on i.fstockid=t.fitemid and t.FProperty=12
join t_icitem t2 on t2.fitemid=i.fitemid
where t2.fnumber not like 'TM-ES%' and t2.fnumber not like 'TM-EF%' and i.fqtyact>0 and i. fdatebackup between @startBackupdate and @endbackupdate
insert into wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,Fitemid,fqty_yl,fqty,ftypeid)--來料不良F1
select @year,@month,'部品',null,i.fitemid,0,i.fqtyact,'來料不良' from icinvbackup i
join t_stock t
on i.fstockid=t.fitemid and t.FProperty=12
join t_icitem t2 on t2.fitemid=i.fitemid
where t2.fnumber not like 'TM-ES%' and t2.fnumber not like 'TM-EF%' and i.fqtyact>0 and i. fdatebackup between @startBackupdate and @endbackupdate
--==================來料不良有半成品成品的化料 =====================
insert into #qty (Fitemid,fnumber,fqty,ftypeid)--來料不良 F1 有半成品成品的化料
select i.fitemid,t2.fnumber,i.fqtyact,'半成品來料不良' from icinvbackup i
join t_stock t
on i.fstockid=t.fitemid and t.FProperty=12 and i.fqtyact>0 and fdatebackup between @startBackupdate and @endbackupdate
join t_icitem t2 on t2.fitemid=i.fitemid
where (t2.fnumber like 'TM-ES%' or t2.fnumber like 'TM-EF%')
If Object_Id( 'Tempdb..#t3') Is Not NULL
drop table #t3
select identity(int,1,1) as 序號 , t1.* into #t3 from #Qty t1 where ftypeid in ('半成品來料不良')
set @n=null
set @fqty_b=null
set @fnumber_b=null
set @n=1
while @n<=(select max(序號) from #t3)
begin
select @fnumber_b=fnumber,@fqty_b=fqty from #t3 where 序號=@n
insert #myallexpand_mx (fnumber_p,fqty_p,fitemid,fqty_yl,llbl,ftypeid)
exec aaa_BomExpand_hg @fnumber_b ,50,1,'1900-01-01 00:00:00','2100-01-01 00:00:00',@fqty_b,'半成品來料不良'
set @n=@n+1
END
insert wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,fitemid,fqty_yl,fqty,ftypeid)
select @year,@month,fnumber_p,fqty_p,fitemid,fqty_yl,llbl,ftypeid from #myallexpand_mx where ftypeid='半成品來料不良'
---==========部品合計===========
insert into #myallexpand_mx (Fitemid,bpLPHJ)--bpLPHJ F2 部品良品合計
select i.fitemid ,i.fqtyact from icinvbackup i
join t_stock t on i.fstockid=t.fitemid and t.FProperty=10
join t_icitem t2 on i.fitemid=t2.fitemid and (t2.fnumber not like 'TM-ES%' AND t2.fnumber not like 'TM-EF%')
where i.fqtyact>0 and i.fdatebackup between @startBackupdate and @endbackupdate
insert into wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,Fitemid,fqty_yl,fqty,ftypeid)--來料不良 F1 有半成品成品的化料
select @year,@month,'部品',null,i.fitemid,0,i.fqtyact,'部品良品合計' from icinvbackup i
join t_stock t on i.fstockid=t.fitemid and t.FProperty=10
join t_icitem t2 on i.fitemid=t2.fitemid and (t2.fnumber not like 'TM-ES%' AND t2.fnumber not like 'TM-EF%')
where i.fqtyact>0 and i.fdatebackup between @startBackupdate and @endbackupdate
-- insert into wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,Fitemid,fqty_yl,fqty,ftypeid)--來料不良 F1 有半成品成品的化料
-- select 2014,11,'部品',null,5496,0,0,'部品良品合計'
--================部品生產不良===========
insert into #myallexpand_mx (Fitemid,bpSCBL)--bpSCBL F3 部品生產不良
select b.fitemid,a.fauxqty
from vwICBill_11 a,t_icitem b
where a.fitemidname=b.fnumber and a. fdate between @startdate and @enddate and FHeadSelfB0434 like '%生產不良%'
and (b.fnumber not like 'TM-ES%' and b.fnumber not like 'TM-EF%')
insert into wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,Fitemid,fqty_yl,fqty,ftypeid)--bpSCBL F3 部品生產不良
select @year,@month,'部品',null,b.fitemid,0,a.fauxqty,'部品生產不良'
from vwICBill_11 a,t_icitem b
where a.fitemidname=b.fnumber and a. fdate between @startdate and @enddate and FHeadSelfB0434 like '%生產不良%'
and (b.fnumber not like 'TM-ES%' and b.fnumber not like 'TM-EF%')
--==================半成品H1H2======================
insert into #Qty ( Fitemid,fnumber,fqty,ftypeid)--半成品良品合計H1
select i.fitemid,t2.fnumber,sum(i.fqtyact),'半成品'
from icinvbackup i
join t_stock t on i.fstockid=t.fitemid and t.FProperty=10
join t_icitem t2 on i.fitemid=t2.fitemid and t2.fnumber like 'TM-ES%'
WHERE i.fqtyact>0 and i.fdatebackup between @startBackupdate and @endbackupdate
group by i.fitemid,t2.fnumber
If Object_Id( 'Tempdb..#t4') Is Not NULL
drop table #t4
select identity(int,1,1) as 序號 , t1.* into #t4 from #Qty t1 where ftypeid in ('半成品')
set @n=null
set @fqty_b=null
set @fnumber_b=null
set @n=1
while @n<=(select max(序號) from #t4)
begin
select @fnumber_b=fnumber,@fqty_b=fqty from #t4 where 序號=@n
insert #myallexpand_mx (fnumber_p,fqty_p,fitemid,fqty_yl,bcpLPHJ,ftypeid)
exec aaa_BomExpand_hg @fnumber_b ,50,1,'1900-01-01 00:00:00','2100-01-01 00:00:00',@fqty_b,'半成品'
set @n=@n+1
END
insert wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,fitemid,fqty_yl,fqty,ftypeid)
select @year,@month,fnumber_p,fqty_p,fitemid,fqty_yl,bcpLPHJ,ftypeid from #myallexpand_mx where ftypeid='半成品'
--==========半成品生產不良===============
insert into #Qty (Fitemid,fnumber,fqty,ftypeid)--半成品生產不良 H2
select b.fitemid,b.fnumber,a.fauxqty,'半成品生產不良' --取領料單中備注附料跟半成品成品的物料
from vwICBill_11 a,t_icitem b
where a.fitemidname=b.fnumber and fdate between @startdate and @enddate and FHeadSelfB0434 like '%生產不良%'
and (b.fnumber like 'TM-ES%' or b.fnumber like 'TM-EF%')
If Object_Id( 'Tempdb..#t5') Is Not NULL
drop table #t5
select identity(int,1,1) as 序號 , t1.* into #t5 from #Qty t1 where ftypeid in ('半成品生產不良')
set @n=null
set @fqty_b=null
set @fnumber_b=null
set @n=1
while @n<=(select max(序號) from #t5)
begin
select @fnumber_b=fnumber,@fqty_b=fqty from #t5 where 序號=@n
insert #myallexpand_mx (fnumber_p,fqty_p,fitemid,fqty_yl,bcpSCBL,ftypeid)
exec aaa_BomExpand_hg @fnumber_b ,50,1,'1900-01-01 00:00:00','2100-01-01 00:00:00',@fqty_b,'半成品生產不良'
set @n=@n+1
END
insert wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,fitemid,fqty_yl,fqty,ftypeid)
select @year,@month,fnumber_p,fqty_p,fitemid,fqty_yl,bcpSCBL,ftypeid from #myallexpand_mx where ftypeid='半成品生產不良'
----***********成品************
insert into #Qty ( Fitemid,fnumber,fqty,ftypeid)--成品良品合計G1
select i.fitemid,t2.fnumber,sum(i.fqtyact),'成品良品合計'
from icinvbackup i
join t_stock t on i.fstockid=t.fitemid and t.FProperty=10
join t_icitem t2 on i.fitemid=t2.fitemid and t2.fnumber like 'TM-EF%'
WHERE i.fqtyact>0 and i.fdatebackup between @startBackupdate and @endbackupdate
group by i.fitemid,t2.fnumber
If Object_Id( 'Tempdb..#t6') Is Not NULL
drop table #t5
select identity(int,1,1) as 序號 , t1.* into #t6 from #Qty t1 where ftypeid in ('成品良品合計')
set @n=null
set @fqty_b=null
set @fnumber_b=null
set @n=1
while @n<=(select max(序號) from #t6)
begin
select @fnumber_b=fnumber,@fqty_b=fqty from #t6 where 序號=@n
insert #myallexpand_mx (fnumber_p,fqty_p,fitemid,fqty_yl,cpLPHJ,ftypeid)
exec aaa_BomExpand_hg @fnumber_b ,50,1,'1900-01-01 00:00:00','2100-01-01 00:00:00',@fqty_b,'成品良品合計'
set @n=@n+1
END
insert wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,fitemid,fqty_yl,fqty,ftypeid)
select @year,@month,fnumber_p,fqty_p,fitemid,fqty_yl,cpLPHJ,ftypeid from #myallexpand_mx where ftypeid='成品良品合計'
----===========成品生產不良======
insert into #Qty ( Fitemid,fnumber,fqty,ftypeid)--成品生產不良 G2
select i.fitemid,t2.fnumber,sum(i.fqtyact),'成品生產不良'
from icinvbackup i
join t_stock t on i.fstockid=t.fitemid and t.FProperty=11
join t_icitem t2 on i.fitemid=t2.fitemid and t2.fnumber like 'TM-EF%'
WHERE i.fqtyact>0 and i.fdatebackup between @startBackupdate and @endbackupdate
group by i.fitemid,t2.fnumber
If Object_Id( 'Tempdb..#t7') Is Not NULL
drop table #t7
select identity(int,1,1) as 序號 , t1.* into #t7 from #Qty t1 where ftypeid in ('成品生產不良')
set @n=null
set @fqty_b=null
set @fnumber_b=null
set @n=1
while @n<=(select max(序號) from #t7)
begin
select @fnumber_b=fnumber,@fqty_b=fqty from #t7 where 序號=@n
insert #myallexpand_mx (fnumber_p,fqty_p,fitemid,fqty_yl,cpSCBL,ftypeid)
exec aaa_BomExpand_hg @fnumber_b ,50,1,'1900-01-01 00:00:00','2100-01-01 00:00:00',@fqty_b,'成品生產不良'
set @n=@n+1
END
insert wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,fitemid,fqty_yl,fqty,ftypeid)
select @year,@month,fnumber_p,fqty_p,fitemid,fqty_yl,cpSCBL,ftypeid from #myallexpand_mx where ftypeid='成品生產不良'
--========客戶資產=============
insert into #myallexpand_mx (Fitemid,KHZC)--客戶資產
select i.fitemid,sum(i.fqtyact)
from icinvbackup i
join t_stock t on i.fstockid=t.fitemid and t.FProperty=13
WHERE i.fqtyact>0 and i.fdatebackup between @startBackupdate and @endbackupdate
group by i.fitemid
insert into wanghx_qty (fyear,fmonth,fnumber_p,fqty_p,Fitemid,fqty_yl,fqty,ftypeid)--客戶資產
select @year,@month,'部品',null,i.fitemid,0,sum(i.fqtyact),'客戶資產'
from icinvbackup i
join t_stock t on i.fstockid=t.fitemid and t.FProperty=13
join t_icitem t2 on i.fitemid=t2.fitemid
WHERE i.fqtyact>0 and i.fdatebackup between @startBackupdate and @endbackupdate
group by i.fitemid,t2.fnumber
insert into #myallexpand ( Fitemid, Fnumber,SYKY,BYSL,BYCH,QTCK,BYTL,bpLPHJ,LLBL,bpSCBL,bcpLPHJ,bcpSCBL,cpLPHJ,cpSCBL,KHZC )
select t1.fitemid,t2.fnumber,SUM(SYKY),SUM(BYSL),SUM(BYCH),SUM(QTCK),SUM(BYTL),sum(bpLPHJ),SUM(LLBL),SUM(bpSCBL),sum(bcpLPHJ),sum(bcpSCBL),SUM(cpLPHJ),SUM(cpSCBL),SUM(KHZC)
from #myallexpand_mx t1,t_icitem t2
where t1.fitemid=t2.fitemid and t2.fdeleted=0
group by t1.fitemid,t2.fnumber
order by t1.fitemid desc
uj5u.com熱心網友回復：
--**********************************************
--顯示報表
--drop table wanghx_hgrp
--**********************************************
select identity(int,1,1) as 序號,@year as 年,@month as 月,t1.* into #wanghx_hgrp from (select t1.fnumber as 新編號,t2.fmodel as 規格型號,t2.fname as 海關名稱,t3.fname as 單位,SYKY as 上月可用,BYSL AS 本月上料,BYCH as 本月出貨,QTCK as 其他出庫,BYTL as 本月退料, LLJC as 理論結存,bpLPHJ as 部品良品合計 ,bcpLPHJ as 半成品良品合計,cpLPHJ as 成品良品合計 ,LLBL AS 來料不良 ,bpSCBL as 部品生產不良 ,cpSCBL+bcpSCBL as 成品半成品不良,KHZC as 客戶資產, PDHJ as 盤點合計,PDHJ-LLJC AS 差異,t2.FGrossWeight as 單重,t2.FGrossWeight* PDHJ as 總重,t2.F_105 as new公司編號
from #myallexpand t1
join t_icitem t2 on t1.fitemid=t2.fitemid
join t_measureunit t3 on t2.funitid=t3.fitemid ) as t1
delete wanghx_hgrp where 年=@year and 月=@month
insert wanghx_hgrp (序號,年,月,新編號,規格型號,海關名稱,單位,上月可用,本月上料,本月出貨,其他出庫,本月退料,理論結存,部品良品合計,半成品良品合計,成品良品合計,來料不良,部品生產不良,成品半成品不良,客戶資產,盤點合計,差異,單重,總重,new公司編號)
select * from #wanghx_hgrp order by 序號
delete from PDHJ where yearint=@yearint and monthint=@monthint
insert into PDHJ(fitemid,fnumber,yearint,monthint,PDHJ) --插入上月可用資料
select t1.fitemid,t2.新編號,@yearint,@monthint,t2.盤點合計
from wanghx_hgrp t2,t_icitem t1
where t1.fnumber=t2.新編號 and t2.年=@year and t2.月=@month
uj5u.com熱心網友回復：
服務器里執行存盤程序與DELPHI呼叫執行是會有些不同，在服務器中(或查詢分析器)執行，程序可以判斷例外，選擇執行，而在DELPHI中執行，程序一定不能有例外，有例外立即退出，后面的部分服務器可能還在執行，但DELPHI不管了轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/90345.html
標籤：數據庫相關
上一篇：【求助】使用mscomm控制元件，如果系統有2個以上的可用串口，如何判斷當前自己的應用程式占用的是哪個串口？
下一篇：DELPHI 的回圈問題。。。