Using cursor in recursive stored procedure

[paragvshah]

I am calling a stored procedure and i am using a Cursor to fetch some rows from table and inserting it in a Temp Table. after inserting the data I am calling the same procedure inside the cursor and then the Cursor is closed.

This gives me an error that cursor is already open.

How can I sort out this problem?

Can any one help me out.

 

[Geri2]

Sorry I couldn't imagine this situation...
But why call you the same procedure in the cursor.

Could you display the Cursor?

Geri

 

[swampBoogie]

Use a local cursor.

 

[paragvshah]

Here is the procedure.

CREATE PROCEDURE [dbo].[usp_SubJobwiseBoms]
@passSrNo int
AS
declare
@CursorStr3 varchar(750),
@getinQty numeric(18,3),
@getinRemarks varchar(100),
@getinItemcd varchar(13),
@getinItemDesc varchar(100)

SET @CursorStr3 = 'DECLARE TableCursor3 CURSOR FOR Select jb.SrNo, jb.Qty, jb.Remarks, i.ItemCode, i.ItemDesc From JobwiseBoms jb INNER JOIN Items i ON (jb.ItemID = i.ItemID) Where ParentSrNo = '+@passSrNo+''
EXEC (@CursorStr3)

OPEN TableCursor3
FETCH NEXT FROM TableCursor3 INTO @passSrNo, @getinQty, @getinRemarks, @getinItemcd, @getinItemDesc
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #TEMPTABLE values(@getinItemcd, @getinItemDesc, @getinQty, @getinRemarks)

EXEC usp_SubJobwiseBoms @passSrNo
(Here i am calling this procedure by itself by passing the @passSrNo)

FETCH NEXT FROM TableCursor3 INTO @passSrNo, @getinQty, @getinRemarks, @getinItemcd, @getinItemDesc
END
CLOSE TableCursor3
DEALLOCATE TableCursor3
GO

 

[nigelrivett]

You might end up with problems exceeding the max nestlevel with the above.
The error is due to the cursor being a crated object and the name still being in scope when it is opened again.
Why do you use a string to declare the cursor?

How about something like this

select @runlevel = 1

select jb.SrNo, jb.Qty, jb.Remarks, i.ItemCode, i.ItemDesc, runlevel = @runlevel
into #TEMPTABLE
From JobwiseBoms jb
INNER JOIN Items i
ON (jb.ItemID = i.ItemID)
Where ParentSrNo = @passSrNo

while @@rowcount > 0
begin
select @runlevel = @runlevel + 1
insert #TEMPTABLE
select jb.SrNo, jb.Qty, jb.Remarks, i.ItemCode, i.ItemDesc, @runlevel
into #TEMPTABLE
From JobwiseBoms jb
INNER JOIN Items i
ON (jb.ItemID = i.ItemID)
Where ParentSrNo in (select distinct SrNo from #TEMPTABLE where runlevel = @runlevel - 1)
end


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.