Stored Procedure---Practice

Stored Procedures
1,fgw_proc1:


CREATE PROCEDURE fgw_proc1 (@begin int, @end int)
As

SET NOCOUNT on
DECLARE @userid int, @handled float, @total float

CREATE TABLE #temp_proc1
(
UserID int,
Handled float,
Total float
)
--get @total
DECLARE CUR_CR CURSOR for SELECT count (*) from AHD. Ahd.call_req where open_date> @begin and open_date< @end
OPEN CUR_CR
FETCH CUR_CR into @total
Close CUR_CR
Deallocate CUR_CR

DECLARE CUR_CTCT CURSOR for the SELECT ID from AHD. Ahd.ctct
OPEN CUR_CTCT
FETCH CUR_CTCT into @userid
While @ @FETCH_STATUS = 0
BEGIN
--get @handle through exec fgw_proc2
EXEC fgw_proc2 @userid, @begin, @end, @handled output
INSERT into #temp_proc1 VALUES (@userid, @handled, @total)
FETCH NEXT from CUR_CTCT into @userid
End
Close CUR_CTCT
Deallocate CUR_CTCT
SELECT * from #temp_proc1
DROP TABLE #temp_proc1


drop procedure Fgw_proc1
EXEC Fgw_proc1 1,1


2,fgw_proc2

CREATE PROCEDURE fgw_proc2 (@userid int, @begin int, @end int, @handled float OUTPUT)
As

SET NOCOUNT on
SET @handled = 0
DECLARE @cr_id int, @zh_id int, @status char (), @to_status char (m), @cnt int, @open_date int
DECLARE Cur_crzh CURSOR for SELECT * from AHD.dbo.FGW_CR_ZH where cnt = @userid
OPEN Cur_crzh
FETCH Cur_crzh into @cr_id, @zh_id, @status, @to_status, @cnt, @open_date
While @ @FETCH_STATUS = 0
BEGIN
DECLARE @count2 int
DECLARE cur_crzh2 CURSOR for SELECT count (*) from AHD.dbo.FGW_CR_ZH where cr_id = @cr_id and open_date> @begin and Open_ date< @end
OPEN CUR_CRZH2
FETCH CUR_CRZH2 into @count2
Close CUR_CRZH2
Deallocate CUR_CRZH2
IF @count2!= 0
SET @handled = @handled + 1/@count2
FETCH NEXT from Cur_crzh into @cr_id, @zh_id, @status, @to_status, @cnt, @open_date
End
Close Cur_crzh
Deallocate Cur_crzh
--select @handled



drop procedure Fgw_proc2
EXEC fgw_proc2 1,1,1

3,fgw_proc3


CREATE PROCEDURE fgw_proc3 (@begin int, @end int)
As

SET NOCOUNT on
DECLARE @cr_id int, @zh_id int, @cnt int, @sym char (m), @time_stamp int, @isOK int

CREATE TABLE #temp_proc3
(
cr_id int,
zh_id int,
CNT int,
IsOK int
)

DECLARE cur_crzhsd CURSOR for SELECT cr.id,zh.id,zh.to_cnt,sd.sym,zh.time_stamp from AHD. Ahd.call_req as CR left OUTER JOIN AHD. Ahd.ztr_his as en on the cr.persid=zh.call_req_id left OUTER JOIN AHD. Ahd.srv_desc as SD on Cr.support_lev=sd.code WHERE cr.type= ' I ' and cr.open_date> @begin and cr.open_date< @end and en. to_status= ' OP '
OPEN CUR_CRZHSD
FETCH cur_crzhsd into @cr_id, @zh_id, @cnt, @sym, @time_stamp
While @ @FETCH_STATUS = 0
BEGIN
--get @handle through exec fgw_proc2
EXEC fgw_proc4 @zh_id, @sym, @time_stamp, @cnt output, @isOK output
INSERT into #temp_proc3 VALUES (@cr_id, @zh_id, @cnt, @isOK)
FETCH NEXT from CUR_CRZHSD into @cr_id, @zh_id, @cnt, @sym, @time_stamp
End
Close CUR_CRZHSD
Deallocate CUR_CRZHSD
SELECT * from #temp_proc3
DROP TABLE #temp_proc3


drop procedure Fgw_proc3
EXEC fgw_proc3 1, 1111111111

4,fgw_proc4

CREATE PROCEDURE fgw_proc4 (@zh_id int, @level char (), @time_stamp int, @cnt int output, @isOK int output)
As

SET NOCOUNT on
SET @isOK = 0
DECLARE Cur_zh CURSOR for SELECT to_cnt,time_stamp from AHD. Ahd.ztr_his WHERE id = @zh_id and To_status in (' L1wip ', ' L2wip ') and time_stamp> @time_stamp
OPEN Cur_zh
DECLARE @time_stamp1 int
SET @time_stamp1 =0
FETCH Cur_zh into @cnt, @time_stamp1
IF @time_stamp1!=0
BEGIN
IF CHARINDEX (' level ', @level) is not NULL and CHARINDEX (' level ', @level)!=0
BEGIN
If @time_stamp1-@time_stamp <600
SET @isOK =1
End
ELSE IF CHARINDEX (' Level two ', @level) is not NULL and CHARINDEX (' Level two ', @level)!=0
BEGIN
If @time_stamp1-@time_stamp <1800
SET @isOK =1
End
ELSE IF CHARINDEX (' Level three ', @level) is not NULL and CHARINDEX (' Level three ', @level)!=0
BEGIN
If @time_stamp1-@time_stamp <1800
SET @isOK =1
End
ELSE IF CHARINDEX (' Level four ', @level) is not NULL and CHARINDEX (' Level four ', @level)!=0
BEGIN
If @time_stamp1-@time_stamp <1800
SET @isOK =1
End
End

Close Cur_zh
Deallocate Cur_zh
--select @isOK, @time_stamp1



drop procedure Fgw_proc4
EXEC fgw_proc4 1, ' 1 ', 1,1,1