cursor not looping through the records

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 2005 Forums

Transact-SQL (2005)

cursor not looping through the records

Author

Topic

noms
Starting Member

22 Posts

Posted - 2009-01-16 : 08:08:35

dear experts
i have the following sql script:
DECLARE
@liDepLinksID dm_ID,
@liTransID INT,
@liMaxMaxID dm_ID,
@liNextID dm_ID,
@aiTransID dm_ID,
@aiDepLinksID dm_ID

SELECT @liNextID = NextID from stdTables where TableName = 'astDepreciationTransactions'
DECLARE max_Cursor cursor FOR
SELECT max(TransactionID) from UDMMaxTrans_perDeprLinkID
OPEN max_Cursor
FETCH NEXT FROM max_Cursor into @liMaxMaxID

while @@fetch_status = 0
begin

Declare Trans_Cursor cursor FOR
SELECT ID, DepreciationLinksID from astDepreciationTransactions

OPEN Trans_Cursor
FETCH NEXT FROM Trans_Cursor INTO @liTransID, @liDepLinksID

WHILE @@fetch_status = 0
BEGIN

IF @liMaxMaxID < @liNextID - 1
BEGIN
SET @liTransID = @liMaxMaxID + 1
PRINT 'TransactionID :' + CAST(@liTransID AS VARCHAR) + ' DepreciationLinkID :' + CAST(@liDepLinksID AS VARCHAR)

EXEC pr_UDMAssetValuesReport @liTransID, @liDepLinksID
EXEC pr_UpdateMaxTransID @liTransID, @liDepLinksID
END
ELSE
BEGIN
print 'TransactionID : ' + Cast(@liTransID as varchar) + ' No Transaction'
END
CLOSE Trans_Cursor
DEALLOCATE Trans_Cursor
FETCH NEXT FROM max_Cursor INTO @liMaxMaxID
END
CLOSE max_Cursor
DEALLOCATE max_Cursor

what should happen is, when this condition
IF @liMaxMaxID < @liNextID - 1 is true
takes the maxID from the first cursor add 1 to equal the transID in the second cursor
SET @liTransID = @liMaxMaxID + 1
and then update tableA and tableB of which TableA is this table UDMMaxTrans_perDeprLinkID from the first cursor
and should go back again and get the maxID from the first cursor and do the same thing,
the query is working for first time and only executing one record and doesn't go back to looping

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-16 : 08:35:35

your max cursor would just retrieve you one record. So it will always run once only.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 09:08:07

whats it that you're trying to achieve? may be you could explain us your reqmnt with some sample data

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources