優化以下OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @Prenum,@Empid,@Unitid,@PresentId,@FromTime,@ToTime,@PreReason
WHILE @@FETCH_STATUS=0
BEGIN
Set @P_SERNO = @Prenum
Set @P_ID_NO_SZ = @Empid
Set @P_DEPT = @Unitid
Set @P_REASON = @PresentId
Set @P_DATETIME_B = @FromTime
Set @P_DATETIME_E = @ToTime
Set @P_FAMILY_TYPE = null
IF @PresentId = 'L26'
BEGIN
Set @P_FAMILY_TYPE = 'L26-13A'
IF OBJECT_ID('tempdb..#a') IS NOT NULL
BEGIN
DROP TABLE #a
END
IF OBJECT_ID('tempdb..#b') IS NOT NULL
BEGIN
DROP TABLE #b
END
IF OBJECT_ID('tempdb..#k') IS NOT NULL
BEGIN
DROP TABLE #k
END
select * into #a from openquery(SAL_HCPPROD,'select * from hcp.HR_FUNERAL ')
select * into #b from openquery(SAL_HCPPROD,'select * from hcp.HR_FAMILY_TYPE ')
select * into #k from openquery(SAL_HCPPROD,'select * from hcp.hr_personnel_base ')
DECLARE @P_DEATH_DATE1 VARCHAR(30) =''
select @P_DEATH_DATE1 = a.DEATH_DATE from #a a
where a.family_type_id = (select b.family_type_id from #b b where family_type = @P_FAMILY_TYPE )
and a.psn_id =(select k.id from #k k where k.id_no_sz = @P_ID_NO_SZ)
SET @P_DEATH_DATE = @P_DEATH_DATE1
END
------------ 向HCP資料庫中INSERT資料
Print '@P_SERNO:' + @P_SERNO
Print '@P_ID_NO_SZ:' +@P_ID_NO_SZ
Print '@P_DEPT:' + @P_DEPT
Print '@P_REASON:' +@P_REASON
Print '@P_DATETIME_B:' + @P_DATETIME_B
Print '@P_DATETIME_E:' + @P_DATETIME_E
Print '@P_FAMILY_TYPE:' + @P_FAMILY_TYPE
Print '@P_DEATH_DATE:'
Print @P_DEATH_DATE
Print '@P_SEGMENT_NO:' + @P_SEGMENT_NO
Print '@P_REMARK:' + @P_REMARK
Print '@P_CONFIRM:' + @P_CONFIRM
Print '@P_PERIOD_MASTER:' + @P_PERIOD_MASTER
Print '@P_PERIOD_DETAIL:' + @P_PERIOD_DETAIL
Print '@P_ERROR_TYPE:' + @P_ERROR_TYPE
Print '@P_CREATE_BY:' + @P_CREATE_BY
Print '@P_FAMILY_NAME:' + @P_FAMILY_NAME
EXECUTE ('BEGIN hcp.P_ABSENCE_INSERT_FLOW_TW(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?); END;',
@P_SERNO, @P_ID_NO_SZ, @P_DEPT, @P_REASON, @P_DATETIME_B, @P_DATETIME_E, @P_FAMILY_TYPE, @P_DEATH_DATE,
@P_HOURS output, @P_DAYS output, @P_SEGMENT_NO, @P_REMARK, @P_ERROR output, @P_CONFIRM, @P_PERIOD_MASTER,
@P_PERIOD_DETAIL, @P_ERROR_TYPE, @P_CREATE_BY, @P_FAMILY_NAME)
AT SAL_HCPPROD
Print '@P_ERROR:'
Print @P_ERROR
IF @P_ERROR = 'OK'
BEGIN
SET @Result = 'Y'
SET @OK=@OK+1
END
ELSE
BEGIN
SET @Result = 'N'
SET @NG=@NG+1
--記錄本次拋轉異常數據
INSERT INTO #PresentToHCP_Log
SELECT @P_SERNO, @P_ID_NO_SZ, @P_DEPT, @P_REASON, @P_DATETIME_B, @P_DATETIME_E, @P_ERROR, @Result, GETDATE()
END
--將拋轉數據存入拋轉記錄表
INSERT INTO PresentToHCP_Log
SELECT @P_SERNO, @P_ID_NO_SZ, @P_DEPT, @P_REASON, @P_DATETIME_B, @P_DATETIME_E, @P_ERROR, @Result, GETDATE()
FETCH NEXT FROM MY_CURSOR INTO @Prenum,@Empid,@Unitid,@PresentId,@FromTime,@ToTime,@PreReason
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
最新发布
本文探讨了使用游标时遇到的死循环问题，解释了直接使用while(@@FETCH_STATUS=0)导致死循环的原因，并提供了正确的解决方案，即在循环体内部指定获取下一行数据。
扫一扫
7972
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
