存储过程---实践

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.

[1] [2] 下一页