SQL PROGRAMMING STYLE- P12:Im mot trying to teach you to program in SQL in this book. You might want to read that again. If that is what you wanted, there are better books. This ought to be the second book you buy, not the first. I assume that you already write SQL at some level and want to get better at it. If you want to learn SQL programming tricks, get a copy of my other book, SQL for Smarties (3rd edition, 2005). | 162 CHAPTER 8 HOW TO WRITE STORED PROCEDURES If I can save a few disk fetches I get a much better return on my efforts than if I write faster executing computations. The seek times have not gotten and are not going to get much better in the foreseeable future. Use CASE Expressions to Replace IF-THEN-ELSE Control Flow Statements As an example of how to do this consider the problem of updating the prices in a bookstore. This is a version of an exercise in an early Sybase SQL training class to show why we needed cursors. We want to take 10 percent off expensive books 25 or more and increase inexpensive books by 10 percent to make up the loss. The following statement is the first impulse of most new SQL programmers but it does not work. CREATE PROCEDURE IncreasePrices LANGUAGE SQL DETERMINISTIC BEGIN UPDATE Books SET price price WHERE price UPDATE Books SET price price WHERE price END A book priced at is reduced to by the first update. Then it is raised to by the second update. Reversing the order of the update statements does not change the problem. The answer given in the course was to use a cursor and to update each book one at a time. This would look something like this BEGIN DECLARE Bookcursor CURSOR FOR SELECT price FROM Books FOR UPDATE . ALLOCATE BookCursor OPEN BookCursor FETCH Bookcursor WHILE FOUND Avoid Portability Problems 163 DO IF price THEN UPDATE Books SET price price WHERE CURRENT OF BookCursor ELSE UPDATE Books SET price price WHERE CURRENT OF BookCursor END IF FETCH NEXT Bookcursor END WHILE CLOSE BookCursor DEALLOCATE BookCursor END But by using a CASE expression to replace the logic you can write UPDATE Books SET price CASE WHEN price THEN price ELSE price END This requires less code and will run faster. The heuristic is to look for nearly identical SQL statements in the branches of an IF statement then replace them inside one statement with a CASE .
