求大侠帮忙看下 这个应该怎么优化，已经试过将游标换成WHILE 循环 效果不大。
SET QUOTED_IDENTIFIER ONSET ANSI_NULLS ON
GO
ALTER PROCEDURE dbo.p_GetSpinfo2
@CKLB int,
@QDDM varchar(20),
@table varchar(150)=''
AS
DECLARE
@Errs int,
@strqry varchar(2000),
@SPDM varchar(20),
@QYDM varchar(20),
@SSRQ datetime,
@KCSL numeric(18,4),
@ZZRKR datetime,
@RKDJBH varchar(20),
@RKSL numeric(18,4),
@ZZFHR datetime,
@DJLX varchar(20),
@FHDJBH varchar(20),
@KHMC varchar(50),
@FHSL numeric(18,4),
@ZZXSR datetime,
@XSDJBH varchar(20),
@SDMC varchar(50),
@XSSL numeric(18,4)
SET @Errs=0
BEGIN TRAN
if exists (select * from tempdb.dbo.sysobjects where name =@table) exec ('drop table '+@table)
CREATE TABLE #temptable (SPDM varchar(20),QYDM varchar(20),SSRQ datetime,
KCSL numeric(18,4),ZZRKR datetime,RKDJBH varchar(20),RKSL numeric(18,4),
ZZFHR datetime,DJLX varchar(20),FHDJBH varchar(20),KHMC varchar(50),FHSL numeric(18,4),
ZZXSR datetime,XSDJBH varchar(20),SDMC varchar(50),XSSL numeric(18,4),LJXS numeric(18,4),
PFSL numeric(18,4),LSSL numeric(18,4) )
if @CKLB=1
begin
declare sourcecursor cursor for
select shangpin.spdm,isnull(spss.QYDM,''),
spss.SSRQ
,(select isnull(sum(SL),0) as KCS from SPKCB,CangKu where SPKCB.CKDM=CangKu.CKDM and SPKCB.SPDM=shangpin.SPDM AND
(
(isnull(spss.QYDM,'')<>'' and CangKu.QYDM=spss.QYDM) or (isnull(spss.QYDM,'')='')
) and CangKu.QDDM=@QDDM and CangKu.XZDM in ('0')) as KCSL
from shangpin
left join spss on spss.spdm=shangpin.spdm
end
else begin
declare sourcecursor cursor for
select shangpin.spdm,isnull(spss.QYDM,''),
spss.SSRQ
,(select isnull(sum(SL),0) as KCS from SPKCB,CangKu where SPKCB.CKDM=CangKu.CKDM and SPKCB.SPDM=shangpin.SPDM AND
(
(isnull(spss.QYDM,'')<>'' and CangKu.QYDM=spss.QYDM) or (isnull(spss.QYDM,'')='')
) and CangKu.QDDM=@QDDM and CangKu.XZDM in ('0','1')) as KCSL
from shangpin
left join spss on spss.spdm=shangpin.spdm
end
open sourcecursor
fetch from sourcecursor into @SPDM,@QYDM,@SSRQ,@KCSL
while @@FETCH_STATUS = 0
begin--7
set @ZZRKR=null
set @RKDJBH=''
set @RKSL=0
set @ZZFHR=null
set @DJLX=''
set @FHDJBH=''
set @KHMC=''
set @FHSL=0
set @ZZXSR=null
set @XSDJBH=''
set @SDMC=''
set @XSSL=0
--取入库数据
select TOP 1 @ZZRKR=SPJHD.RQ,@RKDJBH=SPJHD.DJBH from SPJHDMX,SPJHD
where SPJHDMX.DJBH=SPJHD.DJBH
AND SPJHDMX.SPDM=@SPDM
order by SPJHD.RQ,SPJHD.DJBH
SELECT @RKSL=sum(SPJHDMX.SL)
from SPJHDMX,SPJHD
where SPJHDMX.DJBH=SPJHD.DJBH
and SPJHDMX.SPDM=@SPDM
AND SPJHD.DJBH=@RKDJBH
--取发货数据
select TOP 1 @ZZFHR=RQ,@DJLX=PX,@FHDJBH=DJBH,@KHMC=KHMC from
(
select top 1 QDDBD.YSRQ AS RQ,1 AS PX,QDDBD.DJBH,KEHU.KHMC FROM QDDBD,QDDBDMX,KEHU
WHERE QDDBDMX.DJBH=QDDBD.DJBH AND KEHU.CKDM=QDDBD.DM1
AND QDDBDMX.SPDM=@SPDM
AND ((ISNULL(@QYDM,'')<>'' AND KEHU.QYDM=@QYDM ) OR (ISNULL(@QYDM,'')=''))
union all
select top 1 SDPHD.YSRQ AS RQ,2 AS PX,SDPHD.DJBH,KEHU.KHMC FROM SDPHD,SDPHDMX,KEHU
WHERE SDPHDMX.DJBH=SDPHD.DJBH AND KEHU.CKDM=SDPHD.DM1
AND SDPHDMX.SPDM=@SPDM
AND ((ISNULL(@QYDM,'')<>'' AND KEHU.QYDM=@QYDM ) OR (ISNULL(@QYDM,'')=''))
union all
select top 1 PFXHD.RQ,3 AS PX,PFXHD.DJBH,KEHU.KHMC FROM PFXHD,PFXHDMX,KEHU
WHERE PFXHDMX.DJBH=PFXHD.DJBH AND KEHU.KHDM=PFXHD.DM1
AND PFXHDMX.SPDM=@SPDM
AND ((ISNULL(@QYDM,'')<>'' AND KEHU.QYDM=@QYDM ) OR (ISNULL(@QYDM,'')=''))
) as A
ORDER BY RQ,PX,DJBH
if @DJLX='1'
begin
SELECT @FHSL=sum(QDDBDMX.SL)
from QDDBDMX,QDDBD
where QDDBDMX.DJBH=QDDBD.DJBH
and QDDBDMX.SPDM=@SPDM
AND QDDBD.DJBH=@FHDJBH
set @DJLX='渠道调拨单'
end
else if @DJLX='2'
begin
SELECT @FHSL=sum(SDPHDMX.SL)
from SDPHDMX,SDPHD
where SDPHDMX.DJBH=SDPHD.DJBH
and SDPHDMX.SPDM=@SPDM
AND SDPHD.DJBH=@FHDJBH
set @DJLX='商店配货单'
end
else if @DJLX='3'
begin
SELECT @FHSL=sum(PFXHDMX.SL)
from PFXHDMX,PFXHD
where PFXHDMX.DJBH=PFXHD.DJBH
and PFXHDMX.SPDM=@SPDM
AND PFXHD.DJBH=@FHDJBH
set @DJLX='批发销货单'
end
--取销售数据
select TOP 1 @ZZXSR=LSXHD.RQ,@XSDJBH=LSXHD.DJBH,@SDMC=KEHU.KHMC from LSXHDMX,LSXHD,KEHU
where LSXHDMX.DJBH=LSXHD.DJBH AND LSXHD.DM1=KEHU.KHDM
AND ((ISNULL(@QYDM,'')<>'' AND KEHU.QYDM=@QYDM ) OR (ISNULL(@QYDM,'')=''))
AND LSXHDMX.SPDM=@SPDM
order by LSXHD.RQ,LSXHD.DJBH
SELECT @XSSL=sum(LSXHDMX.SL)
from LSXHDMX,LSXHD
where LSXHDMX.DJBH=LSXHD.DJBH
and LSXHDMX.SPDM=@SPDM
AND LSXHD.DJBH=@XSDJBH
insert into #temptable values(
@SPDM,@QYDM,@SSRQ,
@KCSL,@ZZRKR,@RKDJBH,@RKSL,
@ZZFHR,@DJLX,@FHDJBH,@KHMC,@FHSL,
@ZZXSR,@XSDJBH,@SDMC,@XSSL,0,0,0
)
fetch from sourcecursor into @SPDM,@QYDM,@SSRQ,@KCSL
end
close sourcecursor
deallocate sourcecursor
DECLARE CUR_XH CURSOR FOR SELECT SPDM,ISNULL(QYDM,'') AS QYDM FROM #TEMPTABLE
OPEN CUR_XH
FETCH NEXT FROM CUR_XH INTO @SPDM,@QYDM
WHILE @@FETCH_STATUS=0
BEGIN
IF @QYDM<>''
BEGIN
UPDATE #temptable SET PFSL=B.SL FROM #temptable A,
(SELECT V.SPDM,SUM(SL) AS SL,ISNULL(B.QYDM,'') AS QYDM FROM VW_PFXHMX V LEFT JOIN KEHU B ON V.DM1=B.KHDM WHERE SPDM=@SPDM AND B.QYDM=@QYDM GROUP BY V.SPDM,B.QYDM) B
WHERE A.SPDM=B.SPDM AND A.QYDM=B.QYDM AND A.SPDM=@SPDM AND A.QYDM=@QYDM
SET @Errs=@Errs+@@ERROR
UPDATE #temptable SET LSSL=B.SL FROM #temptable A,
(SELECT V.SPDM,SUM(SL) AS SL,ISNULL(B.QYDM,'') AS QYDM FROM VW_LSXHMX V LEFT JOIN KEHU B ON V.DM1=B.KHDM WHERE SPDM=@SPDM AND B.QYDM=@QYDM GROUP BY V.SPDM,B.QYDM) B
WHERE A.SPDM=B.SPDM AND A.QYDM=B.QYDM AND A.SPDM=@SPDM AND A.QYDM=@QYDM
SET @Errs=@Errs+@@ERROR
END
ELSE
BEGIN
UPDATE #temptable SET PFSL=B.SL FROM #temptable A,
(SELECT V.SPDM,SUM(SL) AS SL FROM VW_PFXHMX V WHERE SPDM=@SPDM GROUP BY V.SPDM) B
WHERE A.SPDM=B.SPDM AND A.SPDM=@SPDM
SET @Errs=@Errs+@@ERROR
UPDATE #temptable SET LSSL=B.SL FROM #temptable A,
(SELECT V.SPDM,SUM(SL) AS SL FROM VW_LSXHMX V WHERE SPDM=@SPDM GROUP BY V.SPDM) B
WHERE A.SPDM=B.SPDM AND A.SPDM=@SPDM
SET @Errs=@Errs+@@ERROR
END
FETCH NEXT FROM CUR_XH INTO @SPDM,@QYDM
END
CLOSE CUR_XH
DEALLOCATE CUR_XH
UPDATE #temptable SET LJXS=PFSL+LSSL
SET @Errs=@Errs+@@ERROR
exec( 'select * into '+@table+' from #temptable ')
drop table #temptable
SET @Errs=@Errs+@@ERROR
if @Errs=0
BEGIN
COMMIT TRAN
SELECT 0
END
ELSE
BEGIN
ROLLBACK TRAN
SELECT -1
END
RETURN
GO
