Avoiding Cursors

Last Post 29 May 2002 05:34 AM by szewczykm. 0 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
szewczykm
New Member
New Member

--
29 May 2002 05:34 AM
I've done some reading and have discovered that as a general rule, cursors should be avoided. I'm not saying they should never ever be used, but the sources I'm reading are saying in a lot of cases, if you can write a SQL query instead of using a cursor, it will perform better.

I've got an example and I was wondering if there's a non-cursor type of alternative. Performance isn't an issue here, this is just an example:

Create Table EnabledJobs (to store the names of jobs that are enabled)

Insert into a table EnabledJobs the names of those jobs that are currently enabled in "Sysjobs"

Load the job names (@e_job) from EnabledJobs into Enabled cursor

loop through data until end and do this on each loop

exec sp_update_job @jobname=@ejob, @enabled=0

close and deallocate


Logically, another way to disable the jobs would be to do something like this:

exec sp_update_job @jobname=(select * from EnabledJobs), @enabled=0

I know that syntax doesn't work. I'm wondering if anyone has ideas on if something like that is possible.


(The next job comes along, reads the EnabledJobs table, re-enables all those jobs, then drops the table)

You are not authorized to post a reply.

Acceptable Use Policy