I have inherited a database that has a lot of cursors in it, and from what I have read, cursors and very slow and resource hungry. I'd like to remove these if possbile and replace with something else. I'm familiar with CTEs, but was wondering if a CTE inside of a WHILE 1 = 1 loop is a best practice or not?
Any ideas?
Where you find a lot of cursors, it often points towards a poor SQL developer being too procedural when writing his SQL code.
Try to find areas where procedural-type code can be converted to SQL's own set-based paradigm.
You should be looking to remove any type of loop really. If a cursor can be rewritten as a set-based statement (no looping) then that is what you should be looking to do.
Converting cursors to another form of looping can reduce overheads, but still will not solve performance problems.
Of course, sometimes there are needs to do loops - but you should generally try to mimimise those to the bare minimum
Why do you think that you need to loop,it is necessary only very rarely. What kinds of things are the cursors doing? It is hard to help without more specific examples.
A few suggestions I have are instead of using the values clause for an insert in a cursor, use the select from the cursor in a set-based insert instead. (Updates and deletes can also be done with joins to the data you picked out in the cursor select rather than one record at a time)
If you are doing special processing of a specific data element, the CASE statment is usually a better, set-based way to handle the same thing.
If you are deciding whether to update or insert, then look at MERGE.
Since your cursors are calling stored procs, your devs made the poor choice to reuse code when it was not appropriate. However, in SQL Server 2008, you now have a new option to send a table as a variable to a stored proc, so you could rewrite the proc to be set-based and not process individual records. Then you no longer need a cursor. You will need to examine where else it is being used to ensure that the single record inserts from the same proc still operate correctly if you do this. Nothing says you can't send a one-row table, but the calling code willhave to send the correct table parameter instead of ten separate fields. Or you could handle this by ditching the stored proc you call and writing a set-based insert.
The business logic can almost certainly be handled with the CASE statement.