这个存储过程是什么意思,怎么使用呀

[xiongying18]

怎么使用SQL语句代替这个存储过程呀



/****** Object: Stored Procedure dbo.sp_ppc_techPlan1 Script Date: 2007-11-2 20:47:57 ******/


-- sp_ppc_techPlan1 '57',''

CREATE PROCEDURE sp_ppc_techPlan1
@paratechno char(2), --工艺编号
@lcErrMsg varchar(500) output
AS
declare @isfinP int
declare @isfinU int
declare @plancleardate datetime
select @isfinP=0
select @isfinU=0


select pdctno,units,planqty,procwip,case when tmpout>planqty then planqty else tmpout end as outqty,scrapunit,substring(convert(char(20),intotime,20),6,11) as intotime,kind,
case when pdctdate is null
then ''
else convert(char(10),pdctdate,111)
end as pdctdate,
chgstocktime as deldate,
lackqty=case when planqty-outqty>0
then planqty-outqty
else 0 end,
batchno,
case when deldate is null
then ''
else convert(char(10),deldate,111)
end as senddate,
byfield1,
finishplan,kind1,
case when kind1='结存' then 'Z' else 'A' end +pdctno+left(batchno,10)+convert(char(2),len(batchno))+substring(batchno,11,len(batchno)) as kind3,
case when kind1='结存' then 'Z' else 'A' end +convert(char(20),pdctdate,111) as kind4,
convert(char(1),isyl)+case when kind1='结存' then 'Z' else 'A' end +case when kind='辅计' then 'A' else case when kind='主计' then 'B' else case when kind='调计' then 'C' else 'Z' end end end
+(ISNULL(convert(char(10),pdctdate,111),' ')+convert(char(20),wiptime,20)+convert(char(2),len(batchno))+rtrim(batchno)) as kind5,
case when kind1='结存' then 'Z' else 'A' end +convert(char(20),intotime) as kind2,intotime as shijian,
case when kind1='结存' then 'Z' else 'A' end +convert(char(20),chgstocktime,111) as kind6,isworking,ispause,tasktime,plantime,
@isfinP as isfinP,@isfinU as isfinU,intoqty,wipstate,byfield5,byfield6,outqty as cpck,byfield8,eqptname as techclass,isyl
into #tmpscjhb1
from proscjhb
where techno=@paratechno
and (procwip<>0 or scrapunit<>0 or planqty<>0 or outqty<>0 or endtime<>'')
and pdctno in (select pdctno from mktxhglb where ppeonly='0')
and (procwip<>0 or scrapunit<>0 or outqty<>0)
order by pdctno

----select * from proscjhb where kind1='结存' and wipstate=1 order by intoqty

select a.odrbno,a.taskno,DATEADD(hour,a.workhour+a.waithour+a.prehour,getdate()) as cacudate,
ceiling(((a.workhour+a.waithour+a.prehour)-datediff(hour,getdate(),b.deldate))/24) as tqdays
into #tqdays
from ppcautoplan a left join mktjhxxb b ON(a.odrbno=b.odrbno and b.state='有效' and b.odrbno<>'')
where a.taskno in (select batchno from #tmpscjhb1)


---select distinct taskno,bcutqty into #tmptlblb from ppctlblb1 where taskno in (select batchno from #tmpscjhb1) and taskno<>''


select IDENTITY(int, 1,1) AS ID_Num,a.pdctno,convert(char(10),case when a.units='U' then f.unitqty*isnull(e.bcutqty,0) else isnull(e.bcutqty,0) end ) as bcutqty,
a.units+' '+convert(char(10),(a.planqty+case when a.units='U' then (isnull(a.byfield8,0)) else convert(decimal(6,0),(isnull(a.byfield8,0))*1.000/f.unitqty) end)) as planqty,
case when a.kind1<>'结存' then case when a.wipstate=1 then '*' else case when a.isworking=0 then ' ' else case when a.ispause=1 then '\' else '/' end end end else '' end
+case when a.kind1='结存' then case when a.intoqty=0 then ' ' else case when a.intoqty=1 then case when a.wipstate=1 then '*' else 'H' end else case when a.wipstate=1 then '*' else 'F' end end end else '' end
+ltrim(rtrim(convert(char(20),a.procwip))) as procworking,
isnull(rtrim(convert(char(6),case when a.isworking=1 then datediff(minute,a.shijian,getdate()) else a.tasktime end)),'') as tasktime,
(case when @paratechno='24' then a.outqty-a.cpck else a.outqty end) as outqty,a.scrapunit,a.intotime,a.kind,isnull(convert(char(4),a.plantime),'0') as plantime,
a.pdctdate,a.deldate,isnull(c.chgcount,0) as chgcount,a.lackqty,a.batchno,a.byfield5,a.senddate,a.byfield1,a.finishplan,a.kind1,
a.kind3,a.kind4,a.kind5,a.kind2,a.kind6,a.procwip,isfinP,isfinU,d.odrkind as odrkind,a.byfield6,a.units,isnull(a.techclass,'') as techclass,
isnull(g.class,0) as class,a.isyl,i.cacudate,i.tqdays
into #tmpOutTbl
from #tmpscjhb1 a
left join (select taskno,count(taskno) as chgcount from ppcjhtzb1 group by taskno) c on (a.batchno=c.taskno)
left join (select taskno,max(kind) as odrkind from mktjhxxb1 group by taskno) d on(left(a.batchno,10)=left(d.taskno,10))
left join ppctlstate e on (a.batchno=e.taskno)
left join mktxhglb f on(a.pdctno=f.pdctno)
left join proscgdb g on(a.batchno=g.batchno)
left join #tqdays i on(a.batchno=i.taskno and i.taskno<>'')
order by a.kind5


if @paratechno='54'
update #tmpOutTbl set planqty=left(planqty,1) where batchno not like '%)%'

select * from #tmpOutTbl





GO

[ZengHD]

很难代替
没必要代替

除非你要换成ACCESS数据库