SQL Question – Use Case Statement or Cursors?

Are you split over whether to use a CASE statement or CURSORS in your SQL code?

torn_photo

This article will help clarify your dilemma.

First of all let’s agree that both methodologies are useful for performing actions on a database table based on processing one SQL data row at a time.

Now let’s look at the main characteristics of each methodology.

CURSORS

Looks more complicated. Support costs are higher.

Best when working with multiple tables and complicated algorithms.

Here is an example of inserting into a second table using the first table.

This rudimentary example processed 25,000 rows in 45 seconds.

cursor_sql

CASE STATEMENT

Looks simpler.  Easier to maintain.

Best with select and where clauses used on a single table.

Here is an example of inserting into a second table using the first table.

This rudimentary example processed 25,000 rows in 137 seconds.

case_sql

CONCLUSION

Each methodology has its advantages and each can help you get where you are going.

Good SQL, good night.

Advertisements