Stored Functions

A stored function is a method-based stored procedure that returns a single value. For example, the following class defines a stored function, Square, that returns the square of a given value:

Class MyApp.Utils Extends %Persistent [DdlAllowed]
{
ClassMethod Square(val As %Integer) As %Integer [SqlProc]
{
    Quit val * val
}
}

A stored function is simply a class method with the SqlProc keyword specified.

Note:

For a stored function, the ReturnResultsets keyword must either be not specified (the default) or prefaced by the keyword Not.

You can use a stored function within an SQL query as if it were a built-in SQL function. The name of the function is the SQL name of the stored function (in this case “Square”) qualified by the schema (package) name in which it was defined (in this case “MyApp”).

The following query uses the Square function:

SELECT Cost, MyApp.Utils_Square(Cost) As SquareCost FROM Products

If you define multiple stored functions within the same package (schema), you must make sure that they have unique SQL names.

The following example defines a table named Sample.Wages that has two defined data fields (properties) and two defined stored functions, TimePlus and DTime:

Class Sample.Wages Extends %Persistent [ DdlAllowed ]
{  
  Property Name As %String(MAXLEN = 50) [ Required ];
  Property Salary As %Integer;
  ClassMethod TimePlus(val As %Integer) As %Integer [ SqlProc ]
  {
   QUIT val * 1.5
  }
  ClassMethod DTime(val As %Integer) As %Integer [ SqlProc ]
  {
   QUIT val * 2
  }
}

The following query uses these stored procedures to return the regular salary, time-and-a-half, and double time salary rates for each employee in the same table, Sample.Wages:

SELECT Name,Salary,
       Sample.Wages_TimePlus(Salary) AS Overtime,
       Sample.Wages_DTime(Salary) AS DoubleTime FROM Sample.Wages

The following query uses these stored procedures to return the regular salary, time-and-a-half, and double time salary rates for each employee in a different table, Sample.Employee:

SELECT Name,Salary,
       Sample.Wages_TimePlus(Salary) AS Overtime,
       Sample.Wages_DTime(Salary) AS DoubleTime FROM Sample.Employee

Privileges