Enforcing Procedural Business Logic Using User-Defined Functions

Enforcing Procedural Business Logic Using User -Defined Functions

• Enforce procedural business logic using user-defined functions.

User-defined functions are useful for modularizing code. The previous examples that determine distances on a grid show how a messy formula can be shortened down to a simple function call, resulting in a more readable query. In addition, if you needed to change the distance formula to determine distance over a sphere instead of a flat grid, you could do that by changing the internal workings of the function without changing the interface to the function. User-defined functions can also encapsulate business rules, especially rules that involve complex formulas, making the overall application maintenance easier.

User-defined functions can and should be used to encapsulate business functions. To extend one of the earlier examples, the following examples examine how the salesman is paid, based on the purchase price, quantity sold, and his commission rate. First, modify the SalesRep table like this:

 alter table SalesRep add ComissionRate float 

You need to populate it with some data, and then you can calculate commissions for a given rep like this:

 CREATE FUNCTION GetCommission ( @SalesRepID int ) RETURNS float AS BEGIN DECLARE @ComissionAmount float SELECT @ComissionAmount = SUM(QtySold * price* CommissionRate) FROM Sales INNER JOIN SalesRep ON Sales.SalesRepID = SalesRep.SalesRepID RETURN @ComissionAmount END 

Calling this function provides the commission for the sales rep. This provides a simple and accurate way to encapsulate and document how this calculation is done, so that it's always done the same way, which is just good business procedure.