How to Convert Cursor Procedure Into Set-based Query T-SQL

Cursors have been taught as a no-no in SQL programming, mostly because it is resource-intensive. Of course, there will be some exceptional cases where the use of cursors is justified. In my case, I used cursors because it feels familiar. If you have learnt some procedural ‘ancient’ programming languages, you would understand what I meant.

However, RBarryYoung’s article part 1 and part 2 , convinced me to shake off my dependency to cursors.

Here are the basic steps to convert a cursor:
1. Use the SELECT Clause of the Declare Cursor statement as the basis for our table source. For Selects and Inserts, we may be able to use the SELECT as-is. For Updates and Deletes, we will probably use the From and Where clauses.
2. Any Set or Select statements in the While loop will become columns outputs.
3. Any Containing IF statement, within the loop but containing everything else, will be added to our Where clause.
4. Any IF statements around a single line will become a CASE function in column outputs

And I might add another step here:
5. If there are more than 1 SELECT statements under different DECLARE CURSOR, then it is a possible JOIN.

I successfully converted some of my stored procedures to set-based and it does make a difference in the total transaction cost. Why don’t you give it a try?

Advertisement