存储过程---实践

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(30) , @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) IS NOT NULL AND CHARINDEX('一级', @level)!=0
   BEGIN
    if @time_stamp1 - @time_stamp <600
    SET @isOK=1
   END
  ELSE IF CHARINDEX('二级', @level) IS NOT NULL AND CHARINDEX('二级', @level)!=0
   BEGIN
    if @time_stamp1 - @time_stamp <1800
    SET @isOK=1
   END
  ELSE IF CHARINDEX('三级', @level) IS NOT NULL AND CHARINDEX('三级', @level)!=0
   BEGIN
    if @time_stamp1 - @time_stamp <1800
    SET @isOK=1
   END
  ELSE IF CHARINDEX('四级', @level) IS NOT NULL AND CHARINDEX('四级', @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

 

上一页  [1] [2]