我的存储过程学习2

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

业务系统需要一个关于合同状态的报表，主要显示合同的状态，地区，合同客户类型，合同金额，利息金额等信息.
在中存在4个表
crec01c,sysc01,sysc03d,crmc02分别是'合同主表','区域及业务伙伴','系统状态代码','法人信息表'
下面第一个存储过程是我第一次写的,执行时间是5秒
 ------------------效率较差的存储过程---------------------------
 CREATETABLE#tmptba--创建一个临时表，用于储存我们的结果
 (
 colIDintIDENTITY(1,1)PRIMARYKEYCLUSTERED,
 khlxvarchar(20),
 dqvarchar(20),
 ywhbvarchar(40),
 htztvarchar(20),
 htbsint,
 htjenumeric(13,2),
 lxjenumeric(13,2)
 )
DECLARE @bkhlxchar(1),@ikhidint,@cywdbdchar(10),@chtztchar(1),@njkjenumeric(13,2),@iqxint,@nhtyllnumeric(8,6)--FORProgress
DECLARE@t_khlxvarchar(20),@t_dqvarchar(20),@t_ywhbvarchar(40),@t_htztvarchar(20),@t_htbsint,@t_htjenumeric(13,2),@t_lxjenumeric(13,2)--FORINSERTINTO#tmptba
--DECLARE@indexint
--SET@index=1
DECLAREcur1CURSORFORSELECTbkhlx,ikhid,cywdbd,chtzt,njkje,iqx,nhtyllFROMcrec01c
OPENcur1
FETCHNEXTFROMcur1INTO@bkhlx,@ikhid,@cywdbd,@chtzt,@njkje,@iqx,@nhtyll
WHILE@@FETCH_STATUS=0
BEGIN
 IF@bkhlx='1'
     BEGIN
         SET@t_khlx='自然人'
         SET@t_dq =(SELECTvjgmcFROMsysc01WHEREcjgdm=left(@cywdbd,6))
     END
 ELSE
     BEGIN
         SET@t_khlx='法人'
         SET@t_dq=(SELECTvjgmcFROMsysc01WHEREcjgdm=(SELECTcbmdmFROMcrmc02WHEREfrid=@ikhid))
     END
   SET@t_htbs=1--@index--合同笔数

   SET@t_ywhb=(SELECTvjgmcFROMsysc01WHEREcjgdm=@cywdbd)--业务伙伴
   SET@t_htzt=(SELECTvsjxcFROMsysc03dWHEREczddm='htzt'ANDcsjxm=@chtzt)--合同状态
   SET@t_htje=@njkje
   SET@t_lxje=@njkje*@iqx*@nhtyll*0.001

  INSERTINTO#tmptba(khlx,dq,ywhb,htzt,htbs,htje,lxje)VALUES(@t_khlx,@t_dq,@t_ywhb,@t_htzt,@t_htbs,@t_htje,@t_lxje)
     --SET@index 1
 FETCHNEXTFROMcur1INTO@bkhlx,@ikhid,@cywdbd,@chtzt,@njkje,@iqx,@nhtyll
END
CLOSEcur1
DEALLOCATEcur1
SELECT*FROM#tmptba
GO
 ------------------效率较高的存储过程执行时间是1秒---------------------------
 CREATETABLE#tmptbl--创建一个临时表，用于储存我们的结果
 (
 colIDintIDENTITY(1,1)PRIMARYKEYCLUSTERED,
 khlxvarchar(20),
 dqvarchar(20),
 ywhbvarchar(40),
 htztvarchar(20),
 htbsint,
 htjenumeric(13,2),
 lxjenumeric(13,2)
 )
INSERTINTO#tmptblSELECTCASEWHENa.bkhlx='1'THEN'自然人'ELSE'法人'ENDASkhlx,dq=(SELECTvjgmcFROMsysc01WHEREcjgdm=left(a.cywdbd,6)),ywhb=(SELECTvjgmcFROMsysc01WHEREcjgdm=a.cywdbd),htzt=(SELECTvsjxcFROMsysc03dWHEREczddm='htzt'ANDcsjxm=a.chtzt),htbs=1,htje=a.njkje,lxje=a.njkje*a.iqx*a.nhtyll*0.001FROMcrec01caWHEREa.bkhlx='1'共2页  第1页 
<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>