endless loop...help please:) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

endless loop...help please:)

Author

Topic

BioMash
Starting Member

8 Posts

Posted - 2005-12-29 : 22:39:53

I have the following stored proc which I was working with yesterday without any prblems, I made some changes and now even when it reaches the end of the cursor it seems to start again....it just keeps running and running:)

some help would be appreciated


ALTER PROCEDURE [dbo].[AA_pricelist_TEST]
	@intRound int = null,
	@decPercentage	decimal(3,2) = null,
	@intProductID int = null,
	@intValueID int = null,
	@intOldValueID int = null,
	@intCurrencyID int = null,
	@intTaxID int = null,
	@fltTaxPercent float = null,
	@fltValueAmount float = null,
	@NewValueAmount smallmoney = null
AS

DECLARE c1 CURSOR FOR

Select pv.intProductID,
v.intValueID,
v.intCurrencyID,
v.intTaxID,
v.fltTaxPercent,
v.fltValueAmount

FROM tblProductValue pv, tblValue v

WHERE pv.intValueID = v.intValueID

OPEN c1

FETCH NEXT FROM c1

INTO @intProductID, @intOldValueID, @intCurrencyID, @intTaxID, @fltTaxPercent, @fltValueAmount

WHILE @@FETCH_STATUS = 0
BEGIN

-- changed to allow passing in of percentage and whole number to round up to.

SET @NewValueAmount = CEILING((@fltValueAmount + (@fltValueAmount * @decPercentage))/@intRound)*@intRound


EXEC sp_Value_Insert @intValueID OUTPUT, @intCurrencyID = @intCurrencyID, @intTaxID = @intTaxID, @fltTaxPercent = @fltTaxPercent, @fltValueAmount = @NewValueAmount

EXEC sp_ProductValue_NewAssign @intOldValueID = @intOldValueID, @intProductID = @intProductID, @intValueID = @intValueID

---- add fetch statement
FETCH NEXT FROM c1

INTO @intProductID, @intOldValueID, @intCurrencyID, @intTaxID, @fltTaxPercent, @fltValueAmount

---
END

CLOSE c1
DEALLOCATE c1

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-12-29 : 23:10:22

Whats the total number of record returned by the query?

Select pv.intProductID,
v.intValueID,
v.intCurrencyID,
v.intTaxID,
v.fltTaxPercent,
v.fltValueAmount
FROM tblProductValue pv, tblValue v
WHERE pv.intValueID = v.intValueID

I think its taking time but not infinite. Also check the execution plan to see if there is proper indexes and they don't need updated statistics.

Regards
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-12-29 : 23:34:20

Hi BioMash
since you are declaring a cursor on the tables and with in the loop you are again inserting and updating the table on which cursor is based hence it is going in infinite loop.

modify the proc to execute the cursor on a tem table.

ALTER PROCEDURE [dbo].[AA_pricelist_TEST]
@intRound int = null,
@decPercentage decimal(3,2) = null,
@intProductID int = null,
@intValueID int = null,
@intOldValueID int = null,
@intCurrencyID int = null,
@intTaxID int = null,
@fltTaxPercent float = null,
@fltValueAmount float = null,
@NewValueAmount smallmoney = null
AS

Select pv.intProductID,
v.intValueID,
v.intCurrencyID,
v.intTaxID,
v.fltTaxPercent,
v.fltValueAmount

into #tempTable
FROM tblProductValue pv, tblValue v

WHERE pv.intValueID = v.intValueID

DECLARE c1 CURSOR FOR

select * from #tempTable

OPEN c1

FETCH NEXT FROM c1

INTO @intProductID, @intOldValueID, @intCurrencyID, @intTaxID, @fltTaxPercent, @fltValueAmount

WHILE @@FETCH_STATUS = 0
BEGIN

-- changed to allow passing in of percentage and whole number to round up to.

SET @NewValueAmount = CEILING((@fltValueAmount + (@fltValueAmount * @decPercentage))/@intRound)*@intRound

EXEC sp_Value_Insert @intValueID OUTPUT, @intCurrencyID = @intCurrencyID, @intTaxID = @intTaxID, @fltTaxPercent = @fltTaxPercent, @fltValueAmount = @NewValueAmount

EXEC sp_ProductValue_NewAssign @intOldValueID = @intOldValueID, @intProductID = @intProductID, @intValueID = @intValueID

---- add fetch statement
FETCH NEXT FROM c1

INTO @intProductID, @intOldValueID, @intCurrencyID, @intTaxID, @fltTaxPercent, @fltValueAmount

---
END

CLOSE c1
DEALLOCATE c1

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-12-29 : 23:50:42

or you can use INSENSITIVE
which does not allow committed deletes and updates made to the underlying tables in subsequent fetches. and rest remains same

DECLARE c1 INSENSITIVE CURSOR FOR

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-12-30 : 07:53:46

or even better ....try to stay away from cursors.

post a meaningful description of what is happenning in the sub-procedures (sp_Value_Insert, sp_ProductValue_NewAssign) and there may be a better non-cursor solution to your problem.
sample code, input data and matching results would help a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources