存储过程---实践

<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>

1,fgw_proc1:


CREATEPROCEDUREfgw_proc1(@beginint,@endint)
AS

   SETNOCOUNTON
   DECLARE@useridint,@handledfloat,@totalfloat

   CREATETABLE#temp_proc1
   (
   useridint,
   handledfloat,
   totalfloat
   )
   --get@total
   DECLAREcur_crCURSORFORSELECTcount(*)FROMAHD.AHD.call_reqwhereopen_date>@beginandopen_date<@end
   OPENcur_cr
   FETCHcur_crINTO@total
   CLOSEcur_cr
   DEALLOCATEcur_cr
   
   DECLAREcur_ctctCURSORFORSELECTidFROMAHD.AHD.ctct
   OPENcur_ctct
   FETCHcur_ctctINTO@userid
   WHILE@@FETCH_STATUS=0
       BEGIN
 --get@handlethroughexecfgw_proc2
 EXECfgw_proc2@userid,@begin,@end,@handledoutput
       INSERTINTO#temp_proc1VALUES(@userid,@handled,@total)
 FETCHNEXTFROMcur_ctctINTO@userid
       END
   CLOSEcur_ctct
   DEALLOCATEcur_ctct
   SELECT*FROM#temp_proc1
   DROPTABLE#temp_proc1


dropprocedurefgw_proc1
execfgw_proc11,1


2,fgw_proc2

CREATEPROCEDUREfgw_proc2(@useridint,@beginint,@endint,@handledfloatOUTPUT)
AS

   SETNOCOUNTON
   SET@handled=0
   DECLARE@cr_idint,@zh_idint,@statuschar(20),@to_statuschar(20),@cntint,@open_dateint
   DECLAREcur_crzhCURSORFORSELECT*FROMAHD.dbo.FGW_CR_ZHwherecnt=@userid
   OPENcur_crzh
   FETCHcur_crzhINTO@cr_id,@zh_id,@status,@to_status,@cnt,@open_date
   WHILE@@FETCH_STATUS=0
       BEGIN
 DECLARE@count2int
       DECLAREcur_crzh2CURSORFORSELECTcount(*)FROMAHD.dbo.FGW_CR_ZHwherecr_id=@cr_idandopen_date>@beginandopen_date<@end
 OPENcur_crzh2
 FETCHcur_crzh2INTO@count2
 CLOSEcur_crzh2
 DEALLOCATEcur_crzh2
 IF@count2!=0
  SET@handled=@handled+1/@count2
 FETCHNEXTFROMcur_crzhINTO@cr_id,@zh_id,@status,@to_status,@cnt,@open_date
       END
   CLOSEcur_crzh
   DEALLOCATEcur_crzh
   --SELECT@handled

 

dropprocedurefgw_proc2
execfgw_proc21,1,1

3,fgw_proc3


CREATEPROCEDUREfgw_proc3(@beginint,@endint)
AS

   SETNOCOUNTON
   DECLARE@cr_idint,@zh_idint,@cntint,@symchar(30),@time_stampint,@isOKint

   CREATETABLE#temp_proc3
   (
   cr_idint,
   zh_idint,
   cntint,共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>