游标死循环

wuxiaoqqqq 2008-05-05 08:25:37
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[usp_CopyCprDetailsInCpr]
(
@pGuid varchar(50),
@pVersionCode varchar(7)
)
AS

BEGIN
DECLARE @pOldCprHeaderID int,
@pNewCprHeaderID int,
@pOldCprWeeklyID int,
@pNewCprWeeklyID int

BEGIN TRANSACTION

Insert into CprHeader
(
NumberOfPages,CompanyID,CompanyName,
Address,LicenseNumber,SpecialityLicenseNumber,
PayrollNumber,StartDate,EndDate,ProjectOrContractNumber,
ProjectID,AssignedProjectID,ProjectName,ProjectLocation,
ProjectRole,UserID,UserTitle,UserName,SignImage,SignImageType,
SignImageName,Status,ResubmitCount,
NonComplianceIssuesText,NonComplianceIssuesHTML,IsCompliant,
IsNonPerformance,IsLastCpr,IsDraft,HasCertificateIssue,
OriginalFile,CreatedBy,IsPaperCopy
)
select NumberOfPages,CompanyID,CompanyName,
Address,LicenseNumber,SpecialityLicenseNumber,
PayrollNumber,StartDate,EndDate,ProjectOrContractNumber,
ProjectID,AssignedProjectID,ProjectName,ProjectLocation,
ProjectRole,UserID,UserTitle,UserName,SignImage,
SignImageType,SignImageName,1,0,
NonComplianceIssuesText,NonComplianceIssuesHTML,1,
0,0,1,HasCertificateIssue,
OriginalFile,CreatedBy,IsPaperCopy
from CprHeader
where Guid=@pGuid


IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN 1
END
SET @pNewCprHeaderID = @@IDENTITY


select @pOldCprHeaderID=CprHeaderID from CprHeader where Guid=@pGuid


DECLARE @WeekNumber tinyint,
@PageNumber int,
@EndDate datetime,
@EmployeeID int,
@EmployeeName varchar(255),
@EmployeeAddress varchar(255),
@EmployeeCity varchar(255),
@EmployeeState varchar(255),
@EmployeeZip varchar(50),
@EmployeeFullAddress varchar(255),
@EmployeeSSN varchar(50),
@WithHoldingExemptions varchar(50),
@RateID int,
@FringeBenefitID int,
@CraftCode varchar(50),
@CraftDescription varchar(255),
@ClassCode varchar(50),
@ClassDescription varchar(255),
@StepCode varchar(50),
@StepDescription varchar(255),
@ApprenticePeriod int,
@ConstructionTrade int,
@RRate decimal(18, 2),
@ORate decimal(18, 2),
@DRate decimal(18, 2),
@GrossThisProject decimal(18, 2),
@GrossAllProjects decimal(18, 2),
@TRHW decimal(18, 2),
@TOHW decimal(18, 2),
@TDHW decimal(18, 2),
@Fed decimal(18, 2),
@FICA decimal(18, 2),
@SIT decimal(18, 2),
@SDI decimal(18, 2),
@Other decimal(18, 2),
@AdditionalDeductions1 decimal(18, 2),
@AdditionalDeductions2 decimal(18, 2),
@AdditionalDeductions3 decimal(18, 2),
@TotalDeductions decimal(18, 2),
@VacationAndHoliday decimal(18, 2),
@HealthAndWelfare decimal(18, 2),
@Pension decimal(18, 2),
@Training decimal(18, 2),
@Fund decimal(18, 2),
@UnionDues decimal(18, 2),
@Travelling decimal(18, 2),
@Savings decimal(18, 2),
@Cash decimal(18, 2),
@AdditionalFringes1 decimal(18, 2),
@AdditionalFringes2 decimal(18, 2),
@AdditionalFringes3 decimal(18, 2),
@NetWages decimal(18, 2),
@CheckNumber varchar(50),
@CheckDate datetime,
@Status int,
@RejectedReason varchar(255),
@IsCompliant bit,
@ModifiedBy int,
@CreatedBy int

DECLARE CursorData CURSOR FOR
Select
CprWeeklyID,WeekNumber, PageNumber, EndDate,
EmployeeID, EmployeeName, EmployeeAddress,
EmployeeCity, EmployeeState, EmployeeZip,
EmployeeFullAddress, EmployeeSSN, WithHoldingExemptions,
RateID,FringeBenefitID,
CraftCode, CraftDescription,
ClassCode, ClassDescription,
StepCode, StepDescription,
ApprenticePeriod, ConstructionTrade,
RRate, ORate, DRate,
GrossThisProject, GrossAllProjects,
TRHW, TOHW, TDHW,
Fed, FICA, SIT, SDI, Other,
AdditionalDeductions1, AdditionalDeductions2, AdditionalDeductions3,
TotalDeductions, VacationAndHoliday,HealthAndWelfare,
Pension,Training,Fund,UnionDues,
Travelling,Savings,Cash,
AdditionalFringes1,AdditionalFringes2,AdditionalFringes3,
NetWages,CheckNumber,CheckDate,Status,RejectedReason,
IsCompliant,ModifiedBy,CreatedBy
From CprWeekly
Where CprHeaderID=@pOldCprHeaderID

OPEN CursorData
FETCH CursorData INTO
@pOldCprWeeklyID,@WeekNumber, @PageNumber, @EndDate,
@EmployeeID, @EmployeeName, @EmployeeAddress,
@EmployeeCity, @EmployeeState, @EmployeeZip,
@EmployeeFullAddress, @EmployeeSSN, @WithHoldingExemptions,
@RateID,@FringeBenefitID,
@CraftCode, @CraftDescription,
@ClassCode, @ClassDescription,
@StepCode, @StepDescription,
@ApprenticePeriod, @ConstructionTrade,
@RRate, @ORate, @DRate,
@GrossThisProject, @GrossAllProjects,
@TRHW, @TOHW, @TDHW,
@Fed, @FICA, @SIT, @SDI, @Other,
@AdditionalDeductions1, @AdditionalDeductions2, @AdditionalDeductions3,
@TotalDeductions, @VacationAndHoliday,@HealthAndWelfare,
@Pension,@Training,@Fund,@UnionDues,
@Travelling,@Savings,@Cash,
@AdditionalFringes1,@AdditionalFringes2,@AdditionalFringes3,
@NetWages,@CheckNumber,@CheckDate,@Status,@RejectedReason,
@IsCompliant,@ModifiedBy,@CreatedBy

WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO CprWeekly
(
CprHeaderID,WeekNumber, PageNumber, EndDate,
EmployeeID, EmployeeName, EmployeeAddress,
EmployeeCity, EmployeeState, EmployeeZip,
EmployeeFullAddress, EmployeeSSN, WithHoldingExemptions,
RateID,FringeBenefitID,
CraftCode, CraftDescription,
ClassCode, ClassDescription,
StepCode, StepDescription,
ApprenticePeriod, ConstructionTrade,
RRate, ORate, DRate,
GrossThisProject, GrossAllProjects,
TRHW, TOHW, TDHW,
Fed, FICA, SIT, SDI, Other,
AdditionalDeductions1, AdditionalDeductions2, AdditionalDeductions3,
TotalDeductions, VacationAndHoliday,HealthAndWelfare,
Pension,Training,Fund,UnionDues,
Travelling,Savings,Cash,
AdditionalFringes1,AdditionalFringes2,AdditionalFringes3,
NetWages,CheckNumber,CheckDate,Status,RejectedReason,
IsCompliant,ModifiedBy,CreatedBy
)
values (
@pOldCprHeaderID,@WeekNumber, @PageNumber, @EndDate,
@EmployeeID, @EmployeeName, @EmployeeAddress,
@EmployeeCity, @EmployeeState, @EmployeeZip,
@EmployeeFullAddress, @EmployeeSSN, @WithHoldingExemptions,
@RateID,@FringeBenefitID,
@CraftCode, @CraftDescription,
@ClassCode, @ClassDescription,
@StepCode, @StepDescription,
@ApprenticePeriod, @ConstructionTrade,
@RRate, @ORate, @DRate,
@GrossThisProject, @GrossAllProjects,
@TRHW, @TOHW, @TDHW,
@Fed, @FICA, @SIT, @SDI, @Other,
@AdditionalDeductions1, @AdditionalDeductions2, @AdditionalDeductions3,
@TotalDeductions, @VacationAndHoliday,@HealthAndWelfare,
@Pension,@Training,@Fund,@UnionDues,
@Travelling,@Savings,@Cash,
@AdditionalFringes1,@AdditionalFringes2,@AdditionalFringes3,
@NetWages,@CheckNumber,@CheckDate,@Status,@RejectedReason,
@IsCompliant,@ModifiedBy,@CreatedBy
)

IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN 2
END

SET @pNewCprWeeklyID = @@IDENTITY

SELECT
@RateID = IsNull(R.RateID,0)
FROM
Rates R
WHERE
R.ActiveRecord = 1
AND R.VersionCode = @pVersionCode
AND R.CraftCode = @CraftCode
AND R.ClassCode = @ClassCode
AND R.ConstructionTrade = @ConstructionTrade
AND IsNull(R.StepCode,'') = IsNull(@StepCode,'')
AND IsNull(R.ApprenticePeriod,0) = IsNull(@ApprenticePeriod,0)

Update CprWeekly
Set RateID=@RateID
Where CprWeeklyID=@pNewCprWeeklyID

Insert Into CprHourly
(
CprWeeklyID,DateWorked,RHW,
OHW,DHW,ActiveRecord
)
select @pNewCprWeeklyID,DateWorked,RHW,
OHW,DHW,ActiveRecord
from CprHourly
where CprWeeklyID=@pOldCprWeeklyID



...全文
214 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
-晴天 2008-05-05
  • 打赏
  • 举报
 回复
改成静态游标试试.
wuxiaoqqqq 2008-05-05
  • 打赏
  • 举报
 回复
也谢谢小楼，你上面那个法子没去试了。
wuxiaoqqqq 2008-05-05
  • 打赏
  • 举报
 回复
谢谢海阔天空，静态游标，又学会了一个东西。
Limpire 2008-05-05
  • 打赏
  • 举报
 回复
[Quote=引用 5 楼 Haiwer 的回复:]
很可能是游标类型问题（代码太长，没仔细看，按下面改试试，不行再说）

SQL codeDECLARECursorDataCURSORFORSelectCprWeeklyID,WeekNumber, PageNumber, EndDate,
...----------------------------〉DECLARECursorDataCURSORLOCAL STATIC READ_ONLYFORSelectCprWeeklyID,WeekNumber, PageNumber, EndDate,
...
[/Quote]
Limpire 2008-05-05
  • 打赏
  • 举报
 回复 
加个标识：new bit



DECLARE CursorData CURSOR FOR Select ... From CprWeekly Where CprHeaderID=@pOldCprHeaderID and new=0



...



insert into CprWeekly(..., new) values (..., 0)

...
昵称被占用了 2008-05-05
  • 打赏
  • 举报
 回复
很可能是游标类型问题（代码太长，没仔细看，按下面改试试，不行再说）

DECLARE CursorData CURSOR FOR 

Select  

CprWeeklyID,WeekNumber, PageNumber, EndDate, 

...

----------------------------〉

DECLARE CursorData CURSOR 

LOCAL STATIC READ_ONLY FOR 

Select  

CprWeeklyID,WeekNumber, PageNumber, EndDate, 

...
wuxiaoqqqq 2008-05-05
  • 打赏
  • 举报
 回复
我找到死循环的原因了，我加入新数据的时候每加一条游标就加一条所以一直会在那加。
INSERT INTO CprWeekly
(
CprHeaderID,WeekNumber, PageNumber, EndDate,
EmployeeID, EmployeeName, EmployeeAddress,
EmployeeCity, EmployeeState, EmployeeZip,
EmployeeFullAddress, EmployeeSSN, WithHoldingExemptions,
RateID,FringeBenefitID,
CraftCode, CraftDescription,
ClassCode, ClassDescription,
StepCode, StepDescription,
ApprenticePeriod, ConstructionTrade,
RRate, ORate, DRate,
GrossThisProject, GrossAllProjects,
TRHW, TOHW, TDHW,
Fed, FICA, SIT, SDI, Other,
AdditionalDeductions1, AdditionalDeductions2, AdditionalDeductions3,
TotalDeductions, VacationAndHoliday,HealthAndWelfare,
Pension,Training,Fund,UnionDues,
Travelling,Savings,Cash,
AdditionalFringes1,AdditionalFringes2,AdditionalFringes3,
NetWages,CheckNumber,CheckDate,Status,RejectedReason,
IsCompliant,ModifiedBy,CreatedBy
)
values (
@pOldCprHeaderID,@WeekNumber, @PageNumber, @EndDate,
@EmployeeID, @EmployeeName, @EmployeeAddress,
@EmployeeCity, @EmployeeState, @EmployeeZip,
@EmployeeFullAddress, @EmployeeSSN, @WithHoldingExemptions,
@RateID,@FringeBenefitID,
@CraftCode, @CraftDescription,
@ClassCode, @ClassDescription,
@StepCode, @StepDescription,
@ApprenticePeriod, @ConstructionTrade,
@RRate, @ORate, @DRate,
@GrossThisProject, @GrossAllProjects,
@TRHW, @TOHW, @TDHW,
@Fed, @FICA, @SIT, @SDI, @Other,
@AdditionalDeductions1, @AdditionalDeductions2, @AdditionalDeductions3,
@TotalDeductions, @VacationAndHoliday,@HealthAndWelfare,
@Pension,@Training,@Fund,@UnionDues,
@Travelling,@Savings,@Cash,
@AdditionalFringes1,@AdditionalFringes2,@AdditionalFringes3,
@NetWages,@CheckNumber,@CheckDate,@Status,@RejectedReason,
@IsCompliant,@ModifiedBy,@CreatedBy
)

就是这段里面用了@pOldCprHeaderID，有什么办法让游标就是最开始select的值，不要随着我的操作而改变。
QQQQAnnie 2008-05-05
  • 打赏
  • 举报
 回复
加点注释吗
wuxiaoqqqq 2008-05-05
  • 打赏
  • 举报
 回复
昨天搞了一天没搞好，今天最后一天了，大家帮帮忙。
wuxiaoqqqq 2008-05-05
  • 打赏
  • 举报
 回复
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN 3
END

FETCH CursorData INTO
@pOldCprWeeklyID,@WeekNumber, @PageNumber, @EndDate,
@EmployeeID, @EmployeeName, @EmployeeAddress,
@EmployeeCity, @EmployeeState, @EmployeeZip,
@EmployeeFullAddress, @EmployeeSSN, @WithHoldingExemptions,
@RateID,@FringeBenefitID,
@CraftCode, @CraftDescription,
@ClassCode, @ClassDescription,
@StepCode, @StepDescription,
@ApprenticePeriod, @ConstructionTrade,
@RRate, @ORate, @DRate,
@GrossThisProject, @GrossAllProjects,
@TRHW, @TOHW, @TDHW,
@Fed, @FICA, @SIT, @SDI, @UnionDues,
@AdditionalDeductions1, @AdditionalDeductions2, @AdditionalDeductions3,
@TotalDeductions, @VacationAndHoliday,@HealthAndWelfare,
@Pension,@Training,@Fund,@UnionDues,
@Travelling,@Savings,@Cash,
@AdditionalFringes1,@AdditionalFringes2,@AdditionalFringes3,
@NetWages,@CheckNumber,@CheckDate,@Status,@RejectedReason,
@IsCompliant,@ModifiedBy,@CreatedBy
END

CLOSE CursorData
DEALLOCATE CursorData

IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN 1
END
ELSE
BEGIN
COMMIT TRANSACTION
RETURN 0
END
END


代码如上，主要是游标无法跳出，本来应该只有十条记录的。
mysql游标死循环_解决MySQL 游标最后一行重复
在使用MySQL游标时，发现最后一行一直被重复读取后来在发现应该把读取到的数据放在下一个循环里面使用可以避免重复代码如下：DELIMITER $$USE `test`$$DROP PROCEDURE IF EXISTS `sp_getAllTableName`$$CREATE ...
mysql游标死循环_MySQL存储过程中游标循环的跳出和继续操作示例
众所周知,MySQL中的游标循环操作常用的有三种,LOOP,REPEAT,WHILE.三种循环,方式大同小异.以前从没用过,所以记下来,方便以后查阅.1.REPEAT代码如下:REPEATStatements;UNTIL expressionEND REPEATdemoDECLARE num INT;...
关于DB2中使用游标中止循环的问题
关于DB2中使用游标中止循环的问题 DB2数据库游标循环中止个人 个人工作中遇到的问题，做一下记录。 问题描述：由于需求，需要通过存储过程实现删除一批表的数据。然而在写存储过程中，发现使用游标的时候，不能很...
sql server 游标continue，总是死循环
也遇上过： 死循环是因为continue后又执行与上次相同的fetch了。在continue前加一个fetch next from就可以了。
MySql存储过程游标循环问题解决过程
MySql存储过程游标循环问题，不进循环解决过程。
应用实例

27,580

社区成员

68,556

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章