How to Eleminate using Cursors?

Question

How to Eleminate using Cursors?

SqlDeveloper2246

SSC Eights!

Points: 886
More actions
May 11, 2008 at 5:44 am

#190486

if i have a table named Table1.
if i want to get some values from this table to use it in some statements, i tried not to use cursors but i didn't find any other solution, because how would i get the values i need without enumerating the rows?
if i had an array i could store this values in an array and use a FOR loop to iterate its items.
so anybody have a solution better than using cursors?
Regards.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 1

You haven't told us what you are trying to do so it's impossible to give a specific answer. "Use it in some statements" doesn't tell us much. If those statements are UPDATE, INSERT, DELETE or SELECT statements then the answer is simple - all of those statements work on SETS of rows just fine without cursors.

If you need more help then please state clearly what end result you are trying to achieve.

SqlDeveloper2246 SSC Eights! Points: 886 More actions · Answer 2

I have a table of sections

I made a select statement to get a list of sections inserted in it, let's say for example that i got 11,12,13

for each value of these, i have to execute some procedures and pass this value for them.

So i have some work to do for each of these values (not just an insert or select statement), this work contains many things, like creating temp tables, executing procedures and inserting rows.

So the only solution i found is to make a Read-Only cursor and then execute those operations, but i still want not to use the cursors.

But this is a general problem i found in dealing with such situations (iterating the rows).

If this problem is still mysterious, i'll put my code here to be more obvious.

Best Regards.

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 3

The problem is that the stored procedures you're calling have been written to only handle 1 section at a time. Usually, the only way to use such a thing is with a cursor or While loop... the best thing to do would be to rewrite the procs so that they're set based instead of RBAR.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 4

If you create stored procs that must be called for each row then by defintion you'll have to call those procs a row at a time. Any such solution is a cursor in conceptual terms, whether or not you use an explicit cursor to do it.

The solution is to avoid limiting yourself in this way. Don't create procs that force you to make successive calls for many rows.

See:

http://www.sommarskog.se/share_data.html