Recursive Cursors

Question

Post reply

Recursive Cursors

g Field

SSC-Addicted

Points: 479
More actions
May 22, 2005 at 3:56 pm

#76316

I am trying to scan a tree based on parent-child relationship.
I have written a Local cursor which reads each record associated with the parent ID which is passed a parameter. For each record, it calls the same store proc with the current Record's ID.
1 - 2
1 - 3
2 - 4
4 - 5
So with the records as above in navigates the first level ok, when processing the second level, the variables appear to get upset and after the first read, either gets end of cursor or continues using the wrong variable values.
It would appear that the variables I've declared in the Store Proc are global, so each iteration impacts the previous levels and the current.
I found a 'Local' parameter for Cursors, but the help appears to say the variables are local.
Any ideas
Cheers

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Rob Farley SSCrazy Points: 2739 More actions · Answer 1

You shouldn't use a cursor for this, you should just use a query.

If you give a few more details, then it should be easier to get some code sorted for you.

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

g Field SSC-Addicted Points: 479 More actions · Answer 2

The code I'm using to navigate the tree is below:

However, when I run this, one iteration affects the others.

CREATE PROCEDURE dbo.sp_Load_CostCode

@PrevCostObj_ID int

AS

DECLARE

-- Cursor Fields

@CostObj_ID int

BEGIN

set nocount on

DECLARE curLevel CURSOR LOCAL FOR

SELECT CostObj_ID,

CostObj_ParentID

FROM dbo.PIMS_HUB_CostObj

WHERE CostObj_ParentID = @PrevCostObj_ID

OPEN CurLevel

FETCH NEXT FROM curLevel INTO

@CostObj_ID, @CostObj_ParentID

--Process until all top level records processed

while (@@Fetch_Status = 0)

BEGIN

update dbo.PIMS_HUB_CostObj

Set CostObj_Value = 'Valid' Where CostObj_ID = @CostObj_ID

Exec sp_Load_CostCode @CostObj_ID

FETCH NEXT FROM curLevel INTO

@CostObj_ID, @CostObj_ParentID

END

CLOSE curLevel

DEALLOCATE curLevel

END

RETURN

You start the storeproc with sp_Load_CostCode(0) to read the whole valid hierarchy

cheers

Graham

Jonathan Tomkins Ten Centuries Points: 1268 More actions · Answer 3

Graham

Which version of SQL Server are you using? I've just tried something similar to your proc on a SQL2K instance and it seemed to work ok - only difference is I did a PRINT parent + child instead of the update. Does your's work ok with a print instead of an update?

Also, I think this method can only work if your hierarchy is at most 32 levels deep since I think SQL Server has a limit of 32 nested proc calls (it's in BOL somewhere). So (assuming I'm not making things up ) is your hierarchy always going to be smaller than this?

Jon

g Field SSC-Addicted Points: 479 More actions · Answer 4

I've had a search and found this article which creates the hierarchy in a sql statements - brill

http://www.sqlteam.com/item.asp?ItemID=8866

Thanks for help

Graham