[存储过程] 存储过程---实践

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 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(20), @to_status char(20), @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(30), @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 zh ON 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 zh.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