473,320 Members | 1,766 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

Jun 29 '06 #1
2 3342
(sa**************@gmail.com) 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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 29 '06 #2
>> 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.
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. <<
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?
Any Help would be appreciated. <<


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.

Jun 30 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: robert | last post by:
Mr. Kyte's article doesn't use cursors, while Mr. Feuerstein's book examples do. my recollection of conventional wisdom is to avoid using cursors. is this difference merely a question of style,...
4
by: R. Z. | last post by:
I 've have a stored procedure that compares fields across databases. In order to do so it requires 2 values it acquires from 2 tables. The search is based on the ID of the data owner and a subject:...
3
by: r rk | last post by:
I am trying to write a utility/query to get a report from a table. Below is the some values in the table: table name: dba_daily_resource_usage_v1...
0
by: satishchandra999 | last post by:
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... ...
0
debasisdas
by: debasisdas | last post by:
SAMPLE CODE USING RECORD =========================== declare cursor c1 is select * from dept; type drec is record (a dept.deptno%type, b dept.dname%type, c dept.loc%type); type ttype is...
0
debasisdas
by: debasisdas | last post by:
RESTRICTIONS ON CURSOR VARIABLES ================================= Currently, cursor variables are subject to the following restrictions: Cannot declare cursor variables in a package spec. ...
0
debasisdas
by: debasisdas | last post by:
This thread contains some useful tips/samples regarding some advance concepts in cursors. FEW MORE EXAMPLES =================== declare er emp%rowtype; cursor c1 is select * from emp; begin...
0
debasisdas
by: debasisdas | last post by:
Sample example to show FOR UPDATE CURSOR ----------------------------------------------------------------------------- DECLARE CURSOR EMPREC IS SELECT * FROM EMP FOR UPDATE OF SAL; MYREC...
0
debasisdas
by: debasisdas | last post by:
Cursor Variable Returning %ROWTYPE ----------------------------------------------------------- DECLARE TYPE TmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; tmp_cv TmpCurTyp;TYPE EmpCurTyp IS REF...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.