Home » Tech Reference » Virtual Procedures

MetaMatrix Features

MetaMatrix Virtual Procedures

MetaMatrix supports the definition of virtual procedures. These are similar to stored procedures in relational database management systems. The MetaMatrix procedural language is used to define virtual procedures. They are defined in MetaMatrix Designer, and can be executed against MetaMatrix using standard SQL.



Overview

Virtual procedures are defined using the MetaMatrix procedural language. A virtual procedure has zero or more input parameters, and a result set return type.

Virtual procedures support the ability to execute queries and other SQL commands, define temporary tables, add data to temporary tables, walk through result sets, use loops, and use conditional logic.


Virtual Procedure Definition

A procedure has the following basic form:

Basic Structure of a Virtual procedure
CREATE VIRTUAL PROCEDURE BEGIN <...> END

The CREATE VIRTUAL PROCEDURE line demarcates the beginning of the procedure.

The BEGIN and END keywords are used to denote block boundaries. If there is more than one statement in the procedure, then the statements must be enclosed within BEGIN and END tags.

Within the body of the procedure, any valid statement may be used.


Procedure Input Parameters

Virtual procedures can take zero or more input parameters. Virtual procedure inputs are defined in the MetaMatrix Designer. Each input has the following information that is used during runtime processing:

  • Name - The name of the input parameter
  • Datatype - The design-time type of the input parameter
  • Default value - The default value if the input parameter is not specified
  • Nullable - NO_NULLS, NULLABLE, NULLABLE_UNKNOWN; parameter is optional if nullable, and is not required to be listed when using named parameter syntax

In addition to these, the following metadata attributes may be specified, though they are not used during runtime processing:

  • Direction - always IN for virtual procedures
  • Length
  • Precision
  • Radix
  • Scale

Referencing Inputs

You reference an input to a virtual procedure by using the fully-qualified name of the param (or less if unambiguous). For example, MyModel.MyProc.Param1.

Example of Referencing an Input Parameter for 'GetBalance' Procedure
CREATE VIRTUAL PROCEDURE BEGIN SELECT Balance FROM MyModel.Accts WHERE MyModel.Accts.AccountID = MyModel.GetBalance.AcctID; END

Example Virtual Procedures

This example is a LOOP that walks through a cursored table and uses CONTINUE and BREAK.

Example 1 - Virtual Procedure Using LOOP, CONTINUE, BREAK

CREATE VIRTUAL PROCEDURE
BEGIN
DECLARE double total;
DECLARE integer transactions;
LOOP ON (SELECT amt, type FROM CashTxnTable) AS txncursor
BEGIN
IF(txncursor.type <> 'Sale')
BEGIN
CONTINUE;
END
ELSE BEGIN
total = (total + txncursor.amt);
transactions = (transactions + 1);
IF(transactions = 100)
BEGIN
BREAK;
END
END
SELECT total, (total / transactions) AS avg_transaction;
END
END

This example is uses conditional logic to determine which of two SELECT statements to execute.

Example 2 - Virtual Procedure with Conditional SELECT
CREATE VIRTUAL PROCEDURE BEGIN DECLARE string VARIABLES.SORTDIRECTION; VARIABLES.SORTDIRECTION = PartsVirtual.OrderedQtyProc.SORTMODE; IF ( ucase(VARIABLES.SORTDIRECTION) = 'ASC' ) BEGIN SELECT * FROM PartsVirtual.SupplierInfo WHERE QUANTITY > PartsVirtual.OrderedQtyProc.QTYIN ORDER BY PartsVirtual.SupplierInfo.PART_ID ; END ELSE BEGIN SELECT * FROM PartsVirtual.SupplierInfo WHERE QUANTITY > PartsVirtual.OrderedQtyProc.QTYIN ORDER BY PartsVirtual.SupplierInfo.PART_ID DESC ; END END

This example is uses a temporary table to set the values for multiple variables from the result of a SELECT statement.

Example 3 - Setting Multiple Variables from a SELECT
declare int a; declare int b; SELECT x, y INTO #temp FROM t; a = SELECT x FROM #temp; b = SELECT y FROM #temp; ...
Note that SELECT is the supported keyword for creating the temp table, as opposed to INSERT.
 

Executing Virtual Procedures

You execute procedures using the SQL EXECUTE command. If the procedure has defined inputs, you specify those in a sequential list, or using "name=value" syntax. You must use the name of the input parameter, scoped by the full procedure name if the parameter name is ambiguous in the context of other elements or variables in the procedure.

Sample Procedure Calls
EXECUTE MyProcedure( v1, v2 ) EXECUTE GetAccountBalance( acctID='12345' ) EXEC RenewLicenses(type='customer',status='active',expires=5) EXEC RenewLicenses( 'customer', 'active' )

Note that either positional or named syntax may be used. In the last procedure, an example was given of calling it either way. The last call did not specify the number of days to check for license expiration, as the model specified that parameter as optional with a default value of 10 days.

A virtual procedure call will return a result set just like any SELECT, so you can use this in many places you can use a SELECT. However, within a virtual procedure itself you cannot always use an EXEC directly. Instead, you use the following syntax:

SELECT * FROM (EXEC ...) AS x

The following are some examples of how you can use the results of a virtual procedure call within a virtual procedure definition:

  • LOOP instruction - you can walk through the results and do work on a row-by-row basis
  • Assignment instruction - you can run a command and set the first column / first row value returned to a variable
  • SELECT * INTO #temp FROM (EXEC ...) AS x - you can select the results from a virtual procedure into a temp table, which you can then query against as if it were a physical table.