Re: Calling a SP inside a cursor loop..

(satishchandra999@xxxxxxxxx) writes:
I have SP, which has a cursor iterations. Need to call another SP for
every loop iteration of the cursor. The pseudo code is as follows..

Create proc1 as
Begin

Variable declrations...
...
While @@Fetch_Status = 0
Fetch next From EffectiveDate_Cursor Into @FLD1,@FLD2
/* Assume If loop count is 3.
and If the Fetch stmt is below the begin Stmt, the loop iterations are
4 else the loop iterations are 2*/
begin
/*Calling my second stored proc with fld1 as a In parameter and Op1
and OP2 Out parameters*/
Exec sp_minCheck @fld1, @OP1 output,@OP2 output
Do something based on Op1 and Op2.
end


The problem I had been facing is that, the when a stored proc is called
within the loop, the proc is getting into infinite loops.

May I guess: the inner process also uses cursors?

Anyway, the proper way to program a cursor loop is:

DECLARE cur INENSITIVE CURSOR FOR
SELECT ...
-- Error handling goes here

OPEN cur

WHILE 1 = 1
BEGIN
FETCH cur INTO @x, @y, ....
IF @@fetch_status <> 0
BREAK

-- Do stuff
END

DEALLOCATE cur

By using only one FETCH statements you avoid funny errors, when you change
the cursor and forgets to change the cursor at the end of the loop. And by
checl @@fetch_status directly after the FETCH, you know that @@fetch_status
relates to that FETCH.


.... and in case no one ever told you before: avoid iterations as much as
you can, and try to always work set-based. Yes, I can understand that you
want to reuse code, and if the oomplexity is high enough it may be
warranted if the number of rows in the cursor is moderate. But the cost
in performance for iterative solutions can be *enourmous*. A database
engine is simply not designed for this type of processing.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages