Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

From reading around the net it seems the use of cursors is frown upon but no one ever seemed to give an alternative to them. In my case I need to set row by row though the results of a select statement and pass a few columns from each row into another stored procedure for processing. The only way I see to do this is to create a cursor and run a while loop to use the cursor to grab each row so the data can be processed. So is using a cursor to step though each row from a select statement for processing the best way to do this or is there another way? In my case I have to make sure every single row is processed.

share|improve this question
    
There really is nothing wrong with using a cursor, for certain uses. If you are creating a process that will be called very frequently, then you almost certainly DON'T want to use a cursor, however, if you are creating a process that only runs once a day (for instance), then perhaps a cursor is the easiest way to "get it done". –  Max Vernon Nov 4 '13 at 19:48
    
The procedure are ran on demand and could be ran multiple times each day, in theory it is only ran 3 times a day (start of day, mid day, end day) but as 20 or so people have access to running it the procedure does have a tendency to get ran more then just 3 times each day. –  Matthew Verstraete Nov 4 '13 at 20:47
add comment

1 Answer

up vote 4 down vote accepted

Ideally, you should change the other stored procedure to work on a set rather than take individual values (or create a new stored procedure that works on the set). If you show the actual logic in the procedure and the schema of the affected table(s), rather than just describe the high level process as a word problem, you can get some ideas about that. One way to avoid calling the procedure multiple times would be to pass the values for the different rows as a single set using a table-valued parameter (but again this requires re-writing the procedure or creating a new one).

In the meantime, if you absolutely must use a loop, you must also be willing to accept the performance consequences. One way to offset those - in cases where you really are using a cursor as a read-only, single pass through a set - is to use the following keywords when declaring your cursor:

DECLARE c CURSOR LOCAL FAST_FORWARD
FOR SELECT ...

More info here:

http://www.sqlperformance.com/2012/09/t-sql-queries/cursor-options

share|improve this answer
    
I am actually in the process of rewriting both procedures but I am not sure what you mean by "to work on a set". I would still consider my self a novice with T-SQL and the only way I know how to take each row and process it via a cursor and a while loop. I can not really show the schema or the logic as the original files are nearly 1000 lines of code for each procedure and as it contains business sensitive items I can not safely put it out. If there is more info that will help with a solution please ask, I will provide as much info as I can. –  Matthew Verstraete Nov 4 '13 at 20:46
    
I don't know if there's an easy, generic way to tell you how to change code I can't see from running one row at a time to operating on the set. If you pass a table-valued parameter, you just change the one-row method of WHERE id = @id to joining against the TVP (JOIN @tvp AS tvp ON tvp.id = table.id). –  Aaron Bertrand Nov 4 '13 at 21:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.