请大家评一下这个存储过程
CREATE PROCEDURE B_SP_AddTmBillEntry @BillType varchar(2),@Sql varchar(2000) AS
----@BillType单据类型 11 蓝字入库 12 蓝字出库 01 红字入库 02 红字出库
declare @vstrsql varchar(5000)
declare @vstrsql1 varchar(3000)
declare @vstrsql2 varchar(3000)
set xact_abort on
set nocount on
Create Table #tempBillno
(
fid int IDENTITY (1, 1) NOT NULL ,
FTranType int,
FBillNo varchar(30),
finterid int,
fitemid int,
FItemNumber varchar(200),
FItemName varchar(200),
FModel varchar(200),
FEntryID int,
fqty int,
FUnitID int,
fjldw varchar(200),
fsfdp varchar(1),
FICMODID int,
fsmsl int,
FStockID int,
FSpid int,
FStockName varchar(80),
FSpName varchar(80),
)
select @vStrSql2 = ' Update icstockbill set block=1 from icstockbill a where '
select @vStrSql = 'select DISTINCT a.FTranType,a.FBillNo,a.finterid,b.fitemid,c.fnumber ,c.fname, isnull(c.fmodel, " ") ,b.FEntryID,abs(b.fqty),b.FUnitID,d.fname ,isnull(c.BSFDP,0) ,b.FICMOInterID, case when FTranType in (41,24) then b.FSCStockID else b.FDCStockID End , case when FTranType in (41,24) then isnull(FSCSPID,0) else isnull(FDCSPID,0) End, ' ' ' ', ' ' ' ' , '
If @BillType = '11 ' or @BillType= '02 '
select @vStrSql1 = '(select isnull(sum(Fqty),0) from B_TDBillR f where f.FInterID=a.finterid and f.FItemID=b.fitemid and f.FstockID=case when a.FTranType in (41,24) then b.FSCStockID else b.FDCStockID end and f.FSPID=case when FTranType in (41,24) then isnull(FSCSPID,0) else isnull(FDCSPID,0) End and f.FBs=0 ) as fsmsl '
else
select @vStrSql1 = '(select isnull(sum(Fqty),0) from B_TDBillC f where f.FInterID=a.finterid and f.FItemID=b.fitemid and f.FstockID=case when a.FTranType in (41,24) then b.FSCStockID else b.FDCStockID end and f.FSPID=case when FTranType in (41,24) then isnull(FSCSPID,0) else isnull(FDCSPID,0) End and f.FBs=0) as fsmsl '
select @vstrsql=@vstrsql+@vstrsql1+ ' from icstockbill a ,icstockbillentry b ,t_icitem c ,t_MeasureUnit d Where a.finterid = b.finterid And b.fitemid = c.fitemid And d.FMeasureUnitID = b.funitid '
select @vstrsql2=@vstrsql2+@sql
select @vstrsql=@vstrsql+ ' and '+@sql+ ' order by a.finterid,b.fentryid '
exec (@vstrsql2)
select @vstrsql= ' insert into #tempbillno(FTranType,FBillNo,finterid,fitemid,FItemNumber,FItemName,FModel,FEntryID,
fqty,FUnitID,fjldw ,fsfdp,FICMODID,
FStockID,FSpid,FStockName ,FSpName ,fsmsl) '+@vstrsql
--print @vstrsql
exec (@vstrsql)
---修改仓库、仓位名称
update t1 set FStockName=t2.fname
from #tempbillno t1,t_item t2 where t1.FStockID=t2.fitemid and t2.fitemclassid=5
update t1 set FSpName=t2.fname
from #tempbillno t1,t_StockPlace t2 where t1.FSPID=t2.fspid
set nocount off
set xact_abort off
select * from #tempbillno order by fid
[解决办法]
想说什么呢？
[解决办法]
可读性太差
[解决办法]
汗一个先~~~~~~~~~`
[解决办法]
这存储过程还真长
