Re: Calling SP from a Cursor Loop

boy I'm glad Steve answered that one!!!

--
Cris Hanna [SBS-MVP]
--------------------------------------
Please do not respond directly to me, but only post in the newsgroup so all can take advantage
"Steve Foster [SBS MVP]" <steve.foster@xxxxxxxxxxxxx> wrote in message news:xn0eo25qm519kjq00v@xxxxxxxxxxxxxxxxxxxxxxx
satishchandra999@xxxxxxxxx wrote:

>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_Cursor cursor for
>select field1,fld2 from tab1,tab2 where tab1.effectivedate<Getdate()
>---/////Assuming the above query would result in 3 records
>Open EffectiveDate_Cursor
>Fetch next From EffectiveDate_Cursor 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_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.
>Any Help would be appreciated.
>
>Satish

Without more detail as to what sp_mincheck is supposed to do, and how it
interacts with the schema that's used in proc1, it's impossible to say
what's happening for certain.

However, it seems likely that sp_mincheck ought to be rewritten as a
function, rather than a stored procedure.

It's also not clear why you're using a cursor.

The logic of your cursor is also sub-optimal as it's possible to call
sp_mincheck when the cursor returns no data.

I use the following cursor logic when writing SPs:

declare cursor Cursor_Name scroll cursor for <select>

open Cursor_Name
fetch first from Cursor_Name into <@variables>
while @@fetch_status = 0
begin
<do something with @variables>
fetch next from Cursor_Name into <@variables>
end
close Cursor_Name
deallocate Cursor_Name

This still isn't quite as defensive as it could be, but it can't
accidentally invoke code with null @variables.

Finally, you're likely to get better help on this question by posting to a
SQL Server newsgroup, rather than this SBS group.

--
Steve Foster [SBS MVP]
---------------------------------------
MVPs do not work for Microsoft. Please reply only to the newsgroups.

Relevant Pages

  • Re: Calling SP from a Cursor Loop
    ... every loop iteration of the cursor. ... Fetch next From EffectiveDate_Cursor Into @FLD1,@FLD2 ... Exec sp_minCheck @fld1, @OP1 output,@OP2 output ... and If the Fetch stmt is below the begin Stmt, the loop iterations are ...
    (microsoft.public.windows.server.sbs)
  • Re: Calling a SP inside a cursor loop..
    ... every loop iteration of the cursor. ... Fetch next From EffectiveDate_Cursor Into @FLD1,@FLD2 ... and If the Fetch stmt is below the begin Stmt, the loop iterations are ... the proper way to program a cursor loop is: ...
    (comp.databases.ms-sqlserver)
  • Re: Opinions on approach, please...
    ... To emulate this I plan on using an SQL cursor. ... Is it because when you FETCH from a cursor you go back to the ... therefore the cursor cannot be referred to in Positioned UPDATE and DELETE ... I just did a test and even without specifying FOR READ ONLY I was able to ...
    (comp.lang.cobol)
  • Re: number of rows in cursor
    ... DECLARE authors_cursor insensitive CURSOR FOR( ... declare @l sysname ... FETCH NEXT FROM authors_cursor into @l ...
    (microsoft.public.sqlserver.programming)
  • Re: Opinions on approach, please...
    ... To emulate this I plan on using an SQL cursor. ... I COULD do it with a resultset if it wasn't in COBOL or could ... Is it because when you FETCH from a cursor you go back to the ...
    (comp.lang.cobol)