Rewriting SQL procedures as SQL user-defined functions

You can rewrite simple SQL procedures as SQL user-defined functions to maximize performance in the database management system.

About this task

Procedures and functions share the fact that their routine-bodies are implemented with a compound block that can contain SQL PL. In both, the same SQL PL statements are included within compound blocks bounded by BEGIN and END keywords.

Procedure

There are some things to note when translating an SQL procedure into an SQL function:

The primary and only reason to do this is to improve routine performance when the logic only queries data.
In a scalar function you might have to declare variables to hold the return value to get around the fact that you cannot directly assign a value to any output parameter of the function. The output value of a user-defined scalar function is only specified in the RETURN statement for the function.
If an SQL function is going to modify data, it must be explicitly created using the MODIFIES SQL clause so that is can contain SQL statements that modify data.

Example

In the example that follows an SQL procedure and an SQL scalar function that are logically equivalent are shown. These two routines functionally provide the same output value given the same input values, however they are implemented and invoked in slightly different ways.


  CREATE PROCEDURE GetPrice (IN Vendor CHAR(20), 
                             IN Pid INT, 
                             OUT price DECIMAL(10,3)) 
  LANGUAGE SQL 
  BEGIN 
  
    IF Vendor = 'Vendor 1' 
     THEN SET price = (SELECT ProdPrice FROM V1Table WHERE Id = Pid); 
    ELSE IF Vendor = 'Vendor 2' 
     THEN SET price = (SELECT Price FROM V2Table 
	                   WHERE Pid = GetPrice.Pid); 
    END IF; 
  END

This procedure takes in two input parameter values and returns an output parameter value that is conditionally determined based on the input parameter values. It uses the IF statement. This SQL procedure is invoked by executing the CALL statement. For example from the CLP, you might execute the following:


  CALL GetPrice( 'Vendor 1',  9456, ?)

The SQL procedure can be rewritten as a logically-equivalent SQL table-function as follows:


  CREATE FUNCTION GetPrice (Vendor CHAR(20), Pid INT)  
      RETURNS  DECIMAL(10,3) 
  LANGUAGE SQL  MODIFIES SQL
  BEGIN 
    DECLARE price DECIMAL(10,3); 

    IF Vendor = 'Vendor 1' 
      THEN SET price = (SELECT ProdPrice FROM V1Table WHERE Id = Pid); 
    ELSE IF Vendor = 'Vendor 2' 
      THEN SET price = (SELECT Price FROM V2Table 
      			      WHERE Pid = GetPrice.Pid); 
    END IF; 
  
    RETURN price; 
  END

This function takes in two input parameters and returns a single scalar value, conditionally based on the input parameter values. It requires the declaration and use of a local variable named price to hold the value to be returned until the function returns whereas the SQL procedure can use the output parameter as a variable. Functionally these two routines are performing the same logic.

Now, of course the execution interface for each of these routines is different. Instead of simply calling the SQL procedure with the CALL statement, the SQL function must be invoked within an SQL statement where an expression is allowed. In most cases this isn't a problem and might actually be beneficial if the intention is to immediately operate on the data returned by the routine. Here are two examples of how the SQL function can be invoked.

It can be invoked using the VALUES statement:


  VALUES (GetPrice('Vendor 1', 9456))

It can also be invoked in a SELECT statement that for example might select values from a table and filter rows based on the result of the function:


  SELECT VName FROM Vendors WHERE GetPrice(Vname, Pid) < 10