From: | satishchandra999(at)gmail(dot)com |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Calling a SP from Curosor loop |
Date: | 2006-06-29 17:00:35 |
Message-ID: | 1151600435.709010.253120@j72g2000cwa.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Craig A. James | 2006-06-30 04:52:42 | explain analyze reports 20x more time than actual |
Previous Message | Ron Mayer | 2006-06-28 15:41:41 | Re: Some performance numbers, with thoughts |