So far, we have looked at stored functions as though they were simply a variant on the stored procedure syntax—a special type of stored procedure that can return a value. While this is certainly a valid use for a stored function, stored functions have an additional and significant role to play: as user-defined functions (UDFs ) within SQL statements.
shown in Example 10-11:
it returns a count of customers by status, with the one-byte status
code decoded into a meaningful description. It also sorts by the
decoded customer status. Notice that we must repeat the rather awkward
CASE statement in both the
SELECT list and the
ORDER BY clause.
Example 10-11. SQL statement with multiple CASE statements
SELECT CASE customer_status WHEN 'U' THEN 'Up to Date' WHEN 'N' THEN 'New' WHEN 'O' THEN 'Overdue' END as Status, count(*) as Count FROM customers GROUP BY customer_status ORDER BY CASE customer_status WHEN 'U' THEN 'Up to Date' WHEN 'N' THEN 'New' WHEN 'O' THEN 'Overdue' END
Now imagine an application with many similar
CASE statements, as well as complex
calculations involving business accounting logic, scattered throughout
our application. Such statements—often with embedded expressions far
more complex than the one shown in Example 10-11—result in code
that is difficult to understand and maintain. Whenever the
CASE constructs or business calculations need to be modified, it will be necessary to find and then modify a large number ...