Calling a SP inside a cursor loop..

[satishchandra999@gmail.com]

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...

declare EffectiveDate_C ursor cursor for
select field1,fld2 from tab1,tab2 where tab1.effectived ate<Getdate()
---/////Assuming the above query would result in 3 records
Open EffectiveDate_C ursor
Fetch next From EffectiveDate_C ursor Into @FLD1,@FLD2
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
While @@Fetch_Status = 0
Fetch next From EffectiveDate_C ursor 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.
Any Help would be appreciated.

Satish

[Erland Sommarskog]

Re: Calling a SP inside a cursor loop..

(satishchandra9 99@gmail.com) writes:[color=blue]
> 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_C ursor 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.[/color]

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@sommarsk og.se

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

[--CELKO--]

Re: Calling a SP inside a cursor loop..

>> I have SP, which has a cursor iterations. Need to call another SP for
every loop iteration of the cursor. <<

No. You need to learn to program in SQL. All you are doing is
mimicing a 1960's 3GL magnetic tape file system . In your pseudo
code, you even refer to fields instead of columns! You put the "sp_"
prefix on procedure names!

Don't you understand that SQL is a non-procedurdal language? You
should write only a few cursors in 20 years, not two in one
application.

Your whole approach to the problem is **fundamentally ** wrong.
[color=blue][color=green]
>> 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. <<[/color][/color]

It is very hard to de-bug code that you will not show us. But when
pseudo code is this awful, I bet that the real code is a total mess.
More cursors? Dynamic SQL? Badly written procedural code with poor
coupling and cohesion?
[color=blue][color=green]
>> Any Help would be appreciated. <<[/color][/color]

You have no idea what you are doing. What you will get on Newsgroups
is a quick kludge to get rid of you, but not any real help. You need
to stop programming and get some education; then get some training.