Calling procedures (with cursors) inside cursor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Calling procedures (with cursors) inside cursor

Author

Topic

randy.fletcher86
Starting Member

2 Posts

Posted - 2012-01-27 : 08:40:02

I'm having a problem with this task:
I need to create an SQL script that
calls 2 separate old stored procedures
(that can not be changed at this point)
for each row in a table A.
Those procedures USE cursors to
INSERT rows into tables B and C.

My solution:
Create Cursor.
Loop through each row in table A
and call both procedures with exec.

Problem: My loop executes only one time

Probable cause: Since both my and the
cursor inside those old stored procedures
use "while @@fetch_status=0" to deterime
if there are more rows in cursor and after
first procedure is done with its own crusor
i'm guessing it resets @@fetch_status to 0.

QUESTION:
1) Is there a way to fix this?
2) Maybe I'm doing this wrong. I've been working with sql
for quite some time now but i alwasy managed to avoid cursors
(maily because they are so goddamn slow), but I don't really know how to do this

Thanks in advance.
Please let me know if i should put some bits of code to make the situaion more clear.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-27 : 08:43:15

looks like not a good way of implementing things!
if you've control over code , i would suggest rewriting it to avoid cursor logic and come up with set based alternative
At least you could replace procedure with function so that you can call them inline without going for cusror

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

randy.fletcher86
Starting Member

2 Posts

Posted - 2012-01-27 : 12:03:01

Thanks for reply,

I know it's not pretty. But as stated before I can't touch those functions. And rewriting that code without cursors and putting into script does not appeal to me.

I actually am considering instead of looping through those rows with a cursor generating a list of exec sp1, exec sp2 for each row with appropriate parameters. This way I could avoid the cursor but it will make my script 4000+ lines long since there are over 2000 rows in table A. It still sucks is what I'm trying to say :)

quote:
Originally posted by visakh16

looks like not a good way of implementing things!
if you've control over code , i would suggest rewriting it to avoid cursor logic and come up with set based alternative
At least you could replace procedure with function so that you can call them inline without going for cusror

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-27 : 13:39:30

is there anyway you can add a parameter to pass list of values to procedure and then parse and use individual values inside?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources