January 15, 2009 at 3:39 am
dear experts
i have two tables "UDMMaxTrans_perDeprLinkID" A and "astDepreciationTransactions" B. the storedproc i created holds Max(ID) from table A then finds Max(ID) + 1 from table B and then insert Max(ID) + 1 into table A and table C.
the script is working fine but not looping through the records:
here's the script, please help:
DECLARE
@liDepLinksIDdm_ID,
@liTransIDdm_ID,
@liMaxMaxIDdm_ID,
@liNextIDdm_ID,
@aiTransIDdm_ID,
@aiDepLinksIDdm_ID
SELECT @liMaxMaxID = max(TransactionID) from UDMMaxTrans_perDeprLinkID
SELECT @liNextID = NextID from stdTables where TableName = 'astDepreciationTransactions'
IF @liMaxMaxID < @liNextID - 1
BEGIN
DECLARE lcursor CURSOR LOCAL FOR
SELECT ID, DepreciationLinksID FROM astDepreciationTransactions
WHERE ID = @liMaxMaxID + 1
OPEN lcursor
WHILE 0 = 0
BEGIN
FETCH NEXT FROM lcursor INTO @liTransID,@liDepLinksID
IF @@fetch_status <> 0
BEGIN
BREAK
END
--insert next transID from astDepreciationTransactions to UDMAssetValueReport
EXEC pr_UDMAssetValuesReport @liTransID, @liDepLinksID
--insert next max(transactionid) from UDMAssetValueReport to UDMMaxTrans_perDeprLinkID
EXEC pr_UpdateMaxTransID @liTransID, @liDepLinksID
END
CLOSE lcursor
DEALLOCATE lcursor
END
January 15, 2009 at 8:09 am
Well.. there seems to several issues you need to addreess.
First your line of code where you say "WHILE 0 =0" is an infinite loop logic. How would zero never equal zero? That has to go or be fixed.
Second after you create the cursor and check fetch_status you never again fetch another record. It should be something more like the following...
Create Cursor
Open Cursor
Fetch Next Row
WHILE @@Fetch_Status <> 0 --Records to process still
BEGIN
EXECUTE Stored Procedures
Fetch Next Row
END
January 15, 2009 at 1:06 pm
Hmm, don't see much wrong, except maybe:
WHERE ID = @liMaxMaxID + 1
You're checking for a specific ID value, not just the first value greater than the prev max. Is it possible the value is something other than just exactly 1 more than the previous high value?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 15, 2009 at 2:18 pm
I see a few things here that would concern me. First off, it looks like you are using a sequence table to manage your PKs, correct? This is not good. Secondly, if you are using a sequence table for this, what happens when you add a second instance of this process into the mix? Well, I'll tell you. You'll get deadlocks and PK violations. Lastly, why do you think this needs to be done in a cursor? All of this can be done without cursoring through your recordset. If you want help re-writing it, post your examlpe with some sample data insert statements.
Use these guidelines when posting please: http://www.sqlservercentral.com/articles/Best+Practices/61537/
January 16, 2009 at 12:47 am
thanks for the response,
i don't know what i'm doing wrong here but here's the scenario
DECLARE
@liDepLinksID dm_ID,
@liTransID dm_ID,
@liMaxMaxID dm_ID,
@liNextID dm_ID,
@aiTransID dm_ID,
@aiDepLinksID dm_ID
SELECT @liMaxMaxID = max(TransactionID) from UDMMaxTrans_perDeprLinkID
SELECT @liNextID = NextID from stdTables where TableName = 'astDepreciationTransactions'
IF @liMaxMaxID < @liNextID - 1
BEGIN
DECLARE lcursor CURSOR LOCAL FOR
SELECT ID, DepreciationLinksID FROM astDepreciationTransactions
WHERE ID = @liMaxMaxID + 1
OPEN lcursor
FETCH NEXT FROM lcursor INTO @liTransID,@liDepLinksID
WHILE @@fetch_status <> 0
BEGIN
EXEC pr_UDMAssetValuesReport @liTransID, @liDepLinksID
EXEC pr_UpdateMaxTransID @liTransID, @liDepLinksID
FETCH NEXT FROM lcursor INTO @liTransID,@liDepLinksID
END
CLOSE lcursor
DEALLOCATE lcursor
SELECT @liMaxMaxID = max(TransactionID) from UDMMaxTrans_perDeprLinkID
this code gets the maxID from the tableA
IF @liMaxMaxID < @liNextID - 1
checks if the ID-1 from tableB is greater than ID from tableA, if it meets the criteria, the following code in the cursor:
SELECT ID, DepreciationLinksID FROM astDepreciationTransactions
WHERE ID = @liMaxMaxID + 1
gets the @liMaxMaxID + 1 from tableC to @liTransID,@liDepLinksID
EXEC pr_UpdateMaxTransID @liTransID, @liDepLinksID
insert @liMaxMaxID + 1 and @liDepLinksID into tableA
lets say i have maxID 165437 in tableA, check for this condition, IF @liMaxMaxID < @liNextID - 1,
and select 165437 + 1 from tableB into @liTransID
then update tableA with 165438 then go back and do this
SELECT @liMaxMaxID = max(TransactionID) from UDMMaxTrans_perDeprLinkID until this condition
IF @liMaxMaxID < @liNextID - 1 is false
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply