Use CASE expressions instead of IF statements

CASE statements can be embedded directly within SQL statements, thereby dramatically reducing the amount of code as well as making the query significantly more legible. In many situations, a single CASE statement can replace the use of a cursor which loops over an IF statement. Consider the following example.

UPDATE Products

   SET sale_price = CASE WHEN retail_price >= 50.00

     THEN retail_price * 0.80;

     ELSE retail_price * 0.90

       END;

Avoid data formatting

Data formatting is the job of the display logic, not the data retrieval code. If the application needs to display the name of the day sourced from a date field in the database, select and return the full date to the application. The display logic can then manipulate the data as required. Obviously, this rule applies specifically to SELECT statements. In the case of INSERT and UPDATE queries, data will need to be formatted in the manner expected by the particular database in question (see Portable Data Types, below).

Application issues arising from the use of portable SQL

For the most part, fully portable SQL can be written using simple statements that only utilise SQL-92 conventions. However, things get a little tricky when queries need to be optimised or other advantages offered by modern-day database engines are on offer. For example, a statement such as the following will run on any SQL-92 compliant server.

SELECT [cols] FROM [tables]

  [WHERE conditions]

  [GROUP BY cols]

  [HAVING conditions]

  [ORDER BY cols]