# MTSQL Interpreter

(Formerly, the Arithmetic Plug-in.)

## Program ID

MetraPipeline.MTSQLInterpreter.1

## Description

The purpose of the arithmetic plug-in is to allow a Systems Integrator to quickly generate business logic that can be added to a MetraTech pipeline. With the arithmetic plug-in, Systems Integrators are not required to directly use the pipeline interface, or even to have the development environment necessary to create COM objects. The business logic is input using a simple language based on Microsoft's T-SQL stored procedure language used in SQL Server (see MTSQL-Supported Language Features for details).

While creating a pipeline, you can add a new arithmetic plug-in using the Arithmetic Plug-in Editor in the Metratech Platform Manager. After creating the plug-in, configure it by specifying the MTSQL procedure definition containing the business logic you want to apply to sessions.

The architecture of the MTSQL interpreter will support the use of the interpreter in a varied collection of computation contexts. However, the primary context in which MTSQL programs will be executed is the MTSQL plug-in. The MTSQL plug-in is an ordinary MetraTech plug-in COM object and is configured and executed in a MetraTech pipeline stage. When an MTSQL plug-in executes, PROCEDURE variables in the MTSQL program will be transparently mapped to like-named session variables within the pipeline that executes the MTSQL plug-in (in fact, the session variables are required have the same name as the PROCEDURE variables except that the leading ampersand is elided). The PROCEDURE will be executed once per session that is passed through the session. All database actions will be executed using the database context configured in the pipeline's dbaccess.xml file.

The MTSQL plug-in is configured by embedding an MTSQL program into the plug-in configuration file (the details of the language are specified in MTSQL-Supported Language Features). Here is a summary:

- Support for built in types: integer, double precision, decimal, Boolean and variable length string

- Statically typed interpreter (type inconsistencies will be caught before runtime)

- Explicit CAST operation for type conversion

- Binary arithmetic operators (+, -, *, /, %) for numeric types

- Unary arithmetic operators (+, -) for numeric types

- Special string operators such as string concatenation (+)

- Local variables

- Function calls

- Embedding of SQL SELECT INTO statements

- PRINT statements for output of string parameters to MetraTech log files

The MTSQL language is missing some features that one might expect in such a language, such as:

- Language does not support automatic conversions between data types

- Language does not support wild cards except as part of SELECT INTO statements

- Language does not support nulls.

The program will be specified in the <Program> element of the plug-in configuration's <configdata> element. It is recommended that the program contents be included in a CDATA section due to the fact that the MTSQL syntax using reserved XML tokens.

Note: As a best practice, it is suggested that you open your program with <![CDATA[ and close with ]]> (CDATA sections are used in XML to escape blocks of text containing characters which might otherwise be recognized as markup data, such as "<" and ">").

## "CREATE PROCEDURE"

MTSQL uses the CREATE PROCEDURE keywords to establish the parameters that are going to be used in an MTSQL "program" (All MTSQL programs must begin with CREATE PROCEDURE). As in T-SQL, each parameter following these keywords needs to begin with an "@" symbol. MTSQL limits the use of the command for this one purpose. Another difference with Microsoft T-SQL is that there is no statement that executes the procedure per se. Rather, the MTSQL plugin implicitly executes the procedure once per session.

## Casting

MTSQL allows you to cast between any two data types. For the most part, the conversions behave as you would expect in any programming language. However, there is some unique behavior in MTSQL that is worth mentioning:

### CAST from ENUM to VARCHAR

The CAST form and ENUM value to a VARCHAR value has the same effects as calling the MTEnumConfig::GetEnumeratorByID() COM API function.

### CAST(expression AS ENUM)

The CAST from a VARCHAR value to an ENUM value has the same effect as calling the MTNameID::GetNameID() COM API function. As such, the value in the VARCHAR variable must be a valid, fully qualified name.

CASTs from all other types proceed by converting the value to an INTEGER and then treating that INTEGER as the enumerated type id of the enumerator in question.

## MTSQL Data Types

Important: Unlike T-SQL, MT-SQL does not support automatic conversion between data types.

Supported data types in MTSQL are similar to T-SQL. Standard supported data types from T-SQL are INTEGER, DATETIME, DECIMAL, VARCHAR, and DOUBLE. For these data types, MTSQL differs from T-SQL in only two respects: in MTSQL there is no maximum length of strings for VARCHAR, and for DECIMAL the precision is always 6 and the scale is always 32.

MTSQL includes three data types that are extensions to T-SQL: ENUM, BOOLEAN, and TIME.

### ENUM:

MTSQL supports the primitive type ENUM. An ENUM value corresponds to a MetraTech Enumerated Type Object. ENUM types can be used in SET statements, SELECT statements and can be used in equality tests. There are no ENUM literals in MTSQL.

Additionally, ENUM types can be CAST to other MTSQL primitive types and other MetraTech primitive types can be CAST to ENUM. See Casting for more information.

Example:

CREATE PROCEDURE testEnum @in VARCHAR @out ENUM

AS

SET @out = CAST(‘Global/BooleanYN/No’ AS ENUM)

BOOLEAN:

MTSQL supports the primitive type BOOLEAN. A BOOLEAN type can take only two values: True and False. BOOLEAN can be used in SET statements, SELECT statements and can be used in equality tests. There are BOOLEAN literals ‘TRUE’ and ‘FALSE’ in the MTSQL language.

Example:

CREATE PROCEDURE testBoolean @in INTEGER @out DECIMAL
AS
DECLARE @test BOOLEAN
SET @test = @in > 199 AND @in < 999
IF @test = TRUE
SET @out = 99.88
ELSE
SET @out = 887.873

TIME:

MTSQL supports the primitive type TIME. A TIME type stores the number of seconds since January 1, 1970 UTC. This is the same representation as a Unix time_t data structure and is compatible with MetraTech TIME session properties. TIME values can be used in SET statements, SELECT statements and comparison operators. There are no time literals in the MTSQL programming language.

## Required Input Properties

None.

## Required Output Properties

None.

## Configuration Tags

Tag Name | Value Type | Description
program | String | Denotes the beginning and end of an MTSQL procedure.

## MTSQL-Supported Language Features

The following table shows the language features/descriptions supported in MTSQL (Each category links to a list of supported keywords/operators).

Note on Sources: Many of these definitions are taken from Microsoft's T-SQL reference, Version 8.

Index | Here is a linked, alphabetical index of MTSQL keywords, operators, and data types.
Aggregate Functions | Aggregate functions perform a calculation on a set of values and return a single value.
Control-of-Flow Language | Imposes conditions on the execution of SQL statements, encloses a series of SQL statements so that groups of statements can be executed, provides for exiting a series of SQL statements.
Comments | Indicates user-provided annotation of code.
"CREATE PROCEDURE" | In MTSQL, these two keywords are used to establish the parameters that are going to be used in the XML file.
Date and Time Functions | These scalar functions perform an operation on a date and time input value and return a string, numeric, or date and time value.
Mathematical Functions | These scalar functions perform a calculation, usually based on input values provided as arguments, and return a numeric value.
Statements | MTSQL allows for SELECT INTO database queries and DECLARE, PRINT, and SET statements
String Functions | These scalar functions perform an operation on a string input value and return a string or numeric value.
System Functions | These scalar functions perform operations on and return information about values, objects, and settings.
Arithmetic Operators | Arithmetic operators perform mathematical operations on two expressions of any of the data types of the numeric data type category.
Bitwise Operators | Bitwise operators perform bit manipulations between two expressions of any of the data types of the integer data type category.
Comparison Operators | Comparison operators test relationships between two expressions. Comparison operators can be used on all expressions except expressions of the text.
Logical Operators | Logical operators test for the truth of some condition. Logical operators, like comparison operators, return a Boolean data type with a value of TRUE or FALSE.

## Data Types

Note: Unlike T-SQL, MT-SQL does not support automatic conversion between data types.

Data Types | Each column, local variable, expression, and parameter has a related data type, which is an attribute that specifies the type of data (integer, character, money, and so on) that the object can hold.

## Alphabetical Index of MTSQL Supported Keywords/Data Types

Note: Although most functions and data types operate identically to Microsoft T-SQL, note that there are some minor differences. Check the entries for keywords for details. Some keywords only have valid usage within SELECT statements. These are marked with an asterisk (*).

Symbols | +, -, *, /, %, /*...*/, &, |, ^, ~, =, >, <, >=, <=, <> | M | MAX*, MIN*
A | AND, AS, AVG* | N | NOT
B | BEGIN, BINARY_CHECKSUM*†, BOOLEAN, BREAK | O | OR
C | CASE, CAST, C0NTINUE, CHECKSUM*, CHECKSUM_AGG*†, COUNT*, CREATE PROCEDURE | P | PRINT
D | DATETIME, DECIMAL, DECLARE, DOUBLE | R | RETURN, ROUND
E | ELSE, END, ENUM | S | SELECT INTO, SET, SUM*, STDEV*, STDEVP*†, SUBSTRING
F | FALSE | T | THEN, TIME, TRUE
G | GETDATE, GETUTCDATE, GROUPING*†, GROUP BY* | U | UPPER
I | IF, INTEGER | V | VAR*, VARCHAR, VARP*†
L | LOWER | W | WHEN, WHILE

*These keywords only have valid usage within SELECT INTO statements.

†These keywords are only valid when the system is using a SQL Server database.

## Control-of-Flow Language

Keyword | Explanation
BEGIN...END | Encloses a series of MTSQL statements so that a group of MTSQL statements can be executed. BEGIN and END are control-of-flow language keywords. Code Example
BREAK | Exits the innermost WHILE loop. Any statements following the BREAK keyword are ignored. BREAK is often, but not always, activated by an IF test. Code Example
CONTINUE | Restarts a WHILE loop. Any statements after the CONTINUE keyword are ignored. CONTINUE is often, but not always, activated by an IF test. Code Example
IF...ELSE | Imposes conditions on the execution of an MTSQL statement. The MTSQL statement following an IF keyword and its condition is executed if the condition is satisfied (when the Boolean expression returns TRUE). The optional ELSE keyword introduces an alternate MTSQL statement that is executed when the IF condition is not satisfied (when the Boolean expression returns FALSE). The ELSE keyword is also used in statements using the CASE keyword (see below). Code Example
RETURN | Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements following RETURN are not executed. Code Example
WHILE | Sets a condition for the repeated execution of an MTSQL statement or statement block. The statements are executed repeatedly as long as the specified condition is true. The execution of statements in the WHILE loop can be controlled from inside the loop with the BREAK and CONTINUE keywords. Code Example

##

## Comments

Keyword | Explanation
/*...*/ | Indicates user-provided text. Comments can be inserted on a separate line, nested (-- only) at the end of a SQL command line, or within a SQL statement. The comment is not evaluated by the server. The text between the /* and */ commenting characters is not evaluated by the server.
-- | Indicates user-provided text. Two hyphens standard indicator for comments. Code Example

##

## "CREATE PROCEDURE"

Keyword | Explanation
CREATE PROCEDURE... AS | In MTSQL, these two keywords are used to establish the name and parameters that are going to be used in the procedure (All MTSQL programs must begin with CREATE PROCEDURE). As in T-SQL, each parameter following these keywords needs to begin with an "@" symbol. MTSQL limits the use of this command for this one purpose. The call to the XML file essentially "invokes" the procedure. The keyword AS specifies the actions the procedure is to take. Code Example

##

## Aggregate Functions

Note: The following are only supported as part of SELECT INTO statements. The usage of these keywords within SQL statements is the same as in T-SQL. Some of these aggregate functions can only be used on SQL Server database.

Note: In SELECT INTO statements, wild cards are permitted.

Keyword | Explanation
AVG | Returns the average of the values in a group. Null values are ignored.
MAX | Returns the maximum value in the expression.
BINARY_CHECKSUM* | Returns the binary checksum value computed over a row of a table or over a list of expressions. BINARY_CHECKSUM can be used to detect changes to a row of a table.
MIN | Returns the minimum value in the expression.
CHECKSUM* | Returns the checksum value computed over a row of a table, or over a list of expressions. CHECKSUM is intended for use in building hash indices.
SUM | Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored.
CHECKSUM_AGG* | Returns the checksum of the values in a group. Null values are ignored.
STDEV | Returns the statistical standard deviation of all values in the given expression.
COUNT | Returns the number of items in a group.
STDEVP* | Returns the statistical standard deviation for the population for all values in the given expression.
VAR | Returns the statistical variance of all values in the given expression.
GROUPING* | Is an aggregate function that causes an additional column to be output with a value of 1 when the row is added by either the CUBE or ROLLUP operator, or 0 when the row is not the result of CUBE or ROLLUP. Grouping is allowed only in the select list associated with a GROUP BY clause that contains either the CUBE or ROLLUP operator.
VARP* | Returns the statistical variance for the population for all values in the given expression.
GROUP BY | Divides a table into groups. Groups can consist of column names or results or computed columns.

*These keywords can only be used with SQL Server databases.

## Date and Time Functions

Keyword | Explanation
GETDATE | Returns the current system date and time in the SQL standard internal format for datetime values. Code Example
GETUTCDATE | Returns the datetime value representing the current UTC time (Universal Time Coordinate or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which SQL Server is running. Code Example

## Mathematical Functions

Keyword | Explanation
ROUND | Returns a numeric expression, rounded to the specified length or precision. Round only takes DECIMAL arguments. Code Example

## String Functions

Keyword/Symbol | Explanation
LOWER | Returns a character expression after converting uppercase character data to lowercase. Code Example
UPPER | Returns a character expression with lowercase character data converted to uppercase. Code Example
SUBSTRING | Returns part of a character, binary, text, or image expression. For more information about the valid Microsoft® SQL Server™ data types that can be used with this function.
+ (Concatenate) | An operator in a string expression that concatenates two or more character or binary strings, columns, or a combination of strings and column names into one expression (a string operator).

## System Functions

Keyword | Explanation
CASE | Evaluates a list of conditions and returns one of multiple possible result expressions. Case operates identically to how it would in T-SQL, using the additional keywords WHEN, THEN, and ELSE. CASE has two formats: For a simple CASE function: WHEN: Is the expression returned when the input expression equals when expression evaluates to TRUE, or the boolean expression evaluates to TRUE. The result expression is any valid SQL Server expression. THEN: Is the expression returned when the input expression equals when expression evaluates to TRUE, or the boolean expression evaluates to TRUE. result expression is any valid SQL Server expression. ELSE: Is the expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. The ELSE result expression is any valid SQL Server expression. The data types of the ELSE result expression and any result expression must be the same or must be an implicit conversion. For a searched CASE function: Evaluates, in the order specified, the boolean expressions for each WHEN clause. Returns a result expression of the first boolean expression that evaluates to TRUE. If no Boolean expression evaluates to TRUE, MTSQL returns the ELSE result expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified. Code Example
CAST | The CAST function converts an expression to a specified datatype. The syntax for the CAST function is CAST (expression AS datatype). Note that unlike MS T-SQL, there are no implicit casts in MTSQL. (see above for more information on casting in MTSQL). Code Example

## Arithmetic Operators

S ymbol | Explanation
+ (Add) | Adds two numbers. Code Example Note: This operator is also used for string concatenation. See entry above.
- (Subtract) | Subtracts two numbers. Code Example
* (Multiply) | Multiplies two expressions (an arithmetic multiplication operator). Code Example
/ (Divide) | Divides one number by another (an arithmetic division operator). Code Example
% (Modulo) | Provides the remainder of one number divided by another. Code Example

## Bitwise Operators

S ymbol | Explanation
& (Bitwise AND) | Performs a bitwise logical AND operation between two integer values. Code Example
| (Bitwise OR) | Performs a bitwise logical OR operation between two given integer values as translated to binary expressions within MTSQL statements. Code Example
^ (Bitwise Exclusive OR) | Performs a bitwise exclusive OR operation between two given integer values as translated to binary expressions within MTSQL statements. Code Example
~ (Bitwise NOT) | Performs a bitwise logical NOT operation for one given integer value as translated to binary expressions within MTSQL statements. Code Example

## Comparison Operators

S ymbol | Explanation
= (Equals) | Compares two expressions (a comparison operator). When you compare nonnull expressions, the result is TRUE if both operands are equal; otherwise, the result is FALSE. In MTSQL, the operator can work in alphabetical expressions as well as numerical. Code Example
> (Greater Than) | Compares two expressions (a comparison operator). When you compare expressions, the result is TRUE if the left operand has a higher value than the right operand; otherwise, the result is FALSE. In MTSQL, the operator can work in alphabetical expressions as well as numerical. Code Example
< (Less Than) | Compares two expressions (a comparison operator). When you compare expressions, the result is TRUE if the left operand has a lower value than the right operand; otherwise, the result is FALSE. In MTSQL, the operator can work in alphabetical expressions as well as numerical.
>= (Greater Than or Equal To) | Compares two expressions (a comparison operator). When you compare expressions, the result is TRUE if the left operand has a higher or equal value than the right operand; otherwise, the result is FALSE. In MTSQL, the operator can work in alphabetical expressions as well as numerical. Code Example
<= (Less Than or Equal To) | Compares two expressions (a comparison operator). When you compare expressions, the result is TRUE if the left operand has a lower or equal value than the right operand; otherwise, the result is FALSE. In MTSQL, the operator can work in alphabetical expressions as well as numerical.
<> (Not Equal To) | Compares two expressions (a comparison operator). When you compare expressions, the result is TRUE if the left operand is not equal to the right operand; otherwise, the result is FALSE. In MTSQL, the operator can work in alphabetical expressions as well as numerical.

## Logical Operators

Keyword | Explanation
OR | Combines two conditions. When more than one logical operator is used in a statement, OR operators are evaluated after AND operators. However, you can change the order of evaluation by using parentheses. Code Example
AND | Combines two Boolean expressions and returns TRUE when both expressions are TRUE. When more than one logical operator is used in a statement, AND operators are evaluated first. You can change the order of evaluation by using parentheses. Code Example
NOT | Negates a Boolean input. Code Example

## Data Types

Keyword | Explanation
INTEGER | As in T-SQL, this is integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Storage size is 4 bytes. Code Example
DATETIME | An MTSQL datetime is stored as an 8-byte real value (double), representing a date between January 1, 100 and December 31, 9999, inclusive. The value 2.0 represents January 1, 1900; 3.0 represents January 2, 1900, and so on. Adding 1 to the value increments the date by a day. The fractional part of the value represents the time of day. Therefore, 2.5 represents noon on January 1, 1900; 3.25 represents 6:00 A.M. on January 2, 1900, and so on. Negative numbers represent dates prior to December 30, 1899. Code Example
DECIMAL | Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1. In MTSQL, the precision is always 6 and the scale is always set to 32. Code Example
VARCHAR | "Character varying". Variable-length non-Unicode data. In MTSQL, there is no maximum length of strings. Code Example
DOUBLE | Double precision. Code Example
TIME* | MTSQL support the primitive type TIME. A TIME type stores the number of seconds since January 1, 1970 UTC. This is the same representation as a Unix time_t data structure and is compatible with MetraTech TIME session properties. TIME values can be used in SET statements, SELECT statements and comparison operators. There are no time literals in the MTSQL programming language.
ENUM* | MTSQL supports the primitive type ENUM. An ENUM value corresponds to a MetraTech Enumerated Type Object. ENUM types can be used in SET statements, SELECT statements and can be used in equality tests. There are no ENUM literals in MTSQL. For more on this data type, see the discussion of MTSQL data types above. Code Example
BOOLEAN* | MTSQL supports the primitive type BOOLEAN. A BOOLEAN type can take only two values: True and False. ENUM types can be used in SET statements, SELECT statements and can be used in equality tests. There are BOOLEAN literals ‘TRUE’ and ‘FALSE’ in the MTSQL language. For more on this data type, see the discussion of MTSQL data types above. Code Example

*Indicates an extension specific to MTSQL. (See above.)

## Boolean Type Keywords

Note: Unlike in T-SQL, in MTSQL TRUE and FALSE are reserved keywords.

Keyword | Explanation
TRUE | A reserved keyword in MTSQL associated with the BOOLEAN DATATYPE
FALSE | A reserved keyword in MTSQL associated with the BOOLEAN DATATYPE. Code Example

## Statements

Keyword | Explanation
DECLARE | Variables are declared in the body of a procedure with the DECLARE statement and are assigned values with SET statement. After declaration, all variables are initialized. Code Example
PRINT | Returns a user-defined message as a string to a VAR or VARCHAR variable. Code Example
SET | Sets the specified local variable, previously created with the DECLARE @local_variable statement, to the given value. This is the only use of SET permitted in MTSQL. Code Example
SELECT INTO | MTSQL also allows for SELECT INTO database queries and allows aggregate functions keywords within these queries. SELECT INTO database queries must take the form of SELECT a, b, c, (etc.) INTO @x, @y, @z, (etc.) FROM A B C (etc.)

## Configuration Examples

### CalcCancellationCharges.xml

(This XML document calculates the charges levied on a customer for cancelling a teleconferencing reservation.)

<xmlconfig>
<mtsysconfigdata>
<effective_date ptype="DATETIME">1998-11-19T00:00:00Z</effective_date>
<timeout ptype="INTEGER">30</timeout>
<configfiletype>CONFIG_DATA</configfiletype>
</mtsysconfigdata>
<mtconfigdata>
<version ptype="INTEGER">1</version>
<!-- First processor configuration -->
<processor>
<name>CalcCancellationCharges</name>
<progid>MetraPipeline.MTSQLInterpreter.1</progid>
<description>Calculates Cancellation Charges</description>
<!--
<autotest>
<file>cancelcharges.xml</file>
</autotest>
-->
<inputs>
</inputs>
<outputs>
</outputs>

<!-- Processor specific configuration data -->
<configdata>
<Program>
<![CDATA[
CREATE PROCEDURE calccancelcharges @ScheduledConnections INTEGER
@CancelRate DECIMAL
@CancelMaxCharge DECIMAL
@CancelChargeType ENUM
@CancelApplicBool BOOLEAN
@CancelCharges DECIMAL
AS
DECLARE @Fixed VARCHAR;
DECLARE @strChargeType VARCHAR;
SET @strChargeType = CAST(@CancelChargeType AS VARCHAR)
SET @CancelCharges = 0.0;
IF (@CancelApplicBool = FALSE)
RETURN;
SET @CancelCharges =
CASE
WHEN (@strChargeType = 'Fixed')
THEN @CancelRate
WHEN (@strChargeType = 'PerPort' AND @CancelRate * CAST(@ScheduledConnections AS DECIMAL) >= @CancelMaxCharge)
THEN @CancelMaxCharge
WHEN @strChargeType = 'PerPort'
THEN @CancelRate * CAST(@ScheduledConnections AS DECIMAL)
ELSE
0.0
END;
]]>
</Program>
</configdata>
</processor>
</mtconfigdata>
</xmlconfig>

### CalcOverusedPorts.xml

(This XML document calculates the actual number of ports, both unused or overused.)

<xmlconfig>
<mtsysconfigdata>
<effective_date ptype="DATETIME">1998-11-19T00:00:00Z</effective_date>
<timeout ptype="INTEGER">30</timeout>
<configfiletype>CONFIG_DATA</configfiletype>
</mtsysconfigdata>
<mtconfigdata>
<version ptype="INTEGER">1</version>
<!-- First processor configuration -->
<processor>
<name>CalcOverusedPorts</name>
<progid>MetraPipeline.MTSQLInterpreter.1</progid>
<description>Calculates Actual Number of Ports - Either unused or overused</description>
<inputs>
</inputs>
<outputs>
</outputs>
<!-- Processor specific configuration data -->
<configdata>
<Program>
<![CDATA[
CREATE PROCEDURE calcports @ScheduledConnections INTEGER
@ActualNumConnections INTEGER
@UserNumConnections INTEGER
@UnusedExcludeRoles BOOLEAN
@UnusedPortChargeDef ENUM
@UnusedThreshFree INTEGER
@UnusedAdvancedPercentage INTEGER
@UnusedPortDelta INTEGER
@NumUnusedPorts INTEGER
AS
DECLARE @PortsNotUsed INTEGER;
DECLARE @AdvancedCalc INTEGER;
DECLARE @NumUsedPorts INTEGER;
DECLARE @PortsUseType VARCHAR;
DECLARE @strChargeDef VARCHAR;
SET @strChargeDef = CAST(@UnusedPortChargeDef AS VARCHAR);
SET @NumUnusedPorts = 0;
SET @PortsUseType = CASE WHEN (@ActualNumConnections > @ScheduledConnections) THEN 'Overused' ELSE 'Underused' END;
IF @PortsUseType = 'Overused'
RETURN;
SET @PortsNotUsed = @ScheduledConnections - @ActualNumConnections;
PRINT CAST(@PortsNotUsed AS VARCHAR)
SET @AdvancedCalc = (@ScheduledConnections * @UnusedAdvancedPercentage / 100) - @ActualNumConnections;
SET @NumUnusedPorts =
CASE
WHEN (@strChargeDef = 'Simple')
THEN @PortsNotUsed
WHEN @strChargeDef = 'Threshold'
THEN @PortsNotUsed - @UnusedThreshFree
WHEN @strChargeDef = 'Advanced' AND @PortsNotUsed > @UnusedPortDelta
THEN @AdvancedCalc
ELSE 0
END;
SET @NumUnusedPorts = CASE WHEN @NumUnusedPorts > 0 THEN @NumUnusedPorts ELSE 0 END;
]>
</Program>
</configdata>
</processor>
</mtconfigdata>
</xmlconfig>

### CalcTotalConfCharge.xml

(This XML document calculates the total charge for a customer after a teleconferencing session.)

<xmlconfig>
<mtsysconfigdata>
<effective_date ptype="DATETIME">1998-11-19T00:00:00Z</effective_date>
<timeout ptype="INTEGER">30</timeout>
<configfiletype>CONFIG_DATA</configfiletype>
</mtsysconfigdata>
<mtconfigdata>
<version ptype="INTEGER">1</version>
<!-- First processor configuration -->
<processor>
<name>CalcTotalConfCharge</name>
<progid>MetraPipeline.MTSQLInterpreter.1</progid>
<description>Calculates Total Conferencing Charges</description>
<!--
<autotest>
<file>cancelcharges.xml</file>
</autotest>
-->
<inputs>
</inputs>
<outputs>
</outputs>

<!-- Processor specific configuration data -->
<configdata>
<Program>
<![CDATA[
CREATE PROCEDURE calctotalcharges @ConnectionTotalAmount DECIMAL
@CancelCharges DECIMAL
@ReservationCharges DECIMAL
@UnusedPortCharges DECIMAL
@OverusedPortCharges DECIMAL
@ConfChargeMinimum DECIMAL
@TotalCallCharges DECIMAL
@AdjustmentAmount DECIMAL
@Amount DECIMAL
AS
SET @TotalCallCharges = 0.0;
SET @Amount = @ConnectionTotalAmount + @CancelCharges + @UnusedPortCharges + @OverusedPortCharges + @ReservationCharges;
SET @AdjustmentAmount =0.0;
IF (@Amount < @ConfChargeMinimum)
BEGIN
SET @AdjustmentAmount = @ConfChargeMinimum - @Amount;
SET @Amount = @ConfChargeMinimum;
END;
SET @TotalCallCharges = @Amount - @ConnectionTotalAmount;
]]>
</Program>
</configdata>
</processor>
</mtconfigdata>
</xmlconfig>

### CalcActualDuration.xml

(This XML document calculates the total duration of a teleconferencing session.)

<xmlconfig>
<mtsysconfigdata>
<effective_date ptype="DATETIME">1998-11-19T00:00:00Z</effective_date>
<timeout ptype="INTEGER">30</timeout>
<configfiletype>CONFIG_DATA</configfiletype>
</mtsysconfigdata>
<mtconfigdata>
<version ptype="INTEGER">1</version>
<!-- First processor configuration -->
<processor>
<name>CalcActualDuration</name>
<progid>MetraPipeline.MTSQLInterpreter.1</progid>
<description>Calculates Conference Actual Duration</description>
<!--
<autotest>
<file>actualduration.xml</file>
</autotest>
-->
<inputs>
</inputs>
<outputs>
</outputs>

<!-- Processor specific configuration data -->
<configdata>
<Program>
<![CDATA[
CREATE PROCEDURE calcactualduration @RawActualDuration INTEGER @ActualDuration DECIMAL
AS
DECLARE @nonFractionalDuration INTEGER;
DECLARE @fraction DECIMAL;
SET @nonFractionalDuration = @RawActualDuration / 60;
SET @ActualDuration = CAST(@RawActualDuration AS DECIMAL) / 60.0;
SET @fraction = @ActualDuration - CAST(@nonFractionalDuration AS DECIMAL);
SET @ActualDuration =
CASE WHEN (@fraction > 0.0)
THEN CAST(@nonFractionalDuration AS DECIMAL) + (@fraction * 0.6)
END;
/*Round it to 2 digits*/
SET @ActualDuration = ROUND(@ActualDuration, 2);
]]>
</Program>
</configdata>
</processor>
</mtconfigdata>
</xmlconfig>

### SetStartTime.xml

(This XML document creates a times stamp indicating when a teleconferencing session began.)

<xmlconfig>
<mtsysconfigdata>
<effective_date ptype="DATETIME">1998-11-19T00:00:00Z</effective_date>
<timeout ptype="INTEGER">30</timeout>
<configfiletype>CONFIG_DATA</configfiletype>
</mtsysconfigdata>
<mtconfigdata>
<version ptype="INTEGER">1</version>
<!-- First processor configuration -->
<processor>
<name>SetStartTime</name>
<progid>MetraPipeline.MTSQLInterpreter.1</progid>
<description>Calculates Total Conferencing Charges</description>
<!--
<autotest>
<file>setstarttime.xml</file>
</autotest>
-->
<inputs>
</inputs>
<outputs>
</outputs>

<!-- Processor specific configuration data -->
<configdata>
<Program>
<![CDATA[
CREATE PROCEDURE settimestamp@ ActualStartTime DATETIME @_Timestamp DATETIME
AS
SET @_Timestamp = @ActualStartTime;
]]>
</Program>
</configdata>
</processor>
</mtconfigdata>
</xmlconfig>

## Brief Code Examples

### WHILE, CONTINUE, BREAK:

CREATE PROCEDURE testNestedWhileWithContinueAndBreak @a INTEGER, @b INTEGER, @c INTEGER, @d INTEGER

AS

DECLARE @counter1 INTEGER

SET @counter1 = 0

SET @d = 0

WHILE @counter1 < @a

BEGIN

IF @counter1 = 1

BEGIN

SET @counter1 = 1 + @counter1

CONTINUE

END

DECLARE @counter2 INTEGER

SET @counter2 = 0

WHILE @counter2 < @c

BEGIN

IF @counter2 = 2

BEGIN

SET @counter2 = 1 + @counter2

BREAK

END

SET @d = @b + @d

SET @counter2 = 1 + @counter2

END

SET @d = @b + @d

SET @counter1 = 1 + @counter1

END

### GETDATE, GETUTDATE, DOUBLE:

CREATE PROCEDURE testDate @a DATETIME @b DATETIME @c DOUBLE

AS

SET @a = getdate()

SET @b = getutcdate()

SET @c = CAST(@b AS DOUBLE) - CAST(@a AS DOUBLE)

### LOWER:

CREATE PROCEDURE testLower @a VARCHAR, @d VARCHAR

AS

SET @d = lower(@a)

### UPPER:

CREATE PROCEDURE testUpper @a VARCHAR, @d VARCHAR

AS

SET @d = upper(@a)

### % (Modulo):

CREATE PROCEDURE testVarcharMod @a VARCHAR @b VARCHAR @c VARCHAR AS SET @a = @b % @c

### (Bitwise AND):

CREATE PROCEDURE testIntegerBitwiseAnd @a INTEGER @b INTEGER @c INTEGER AS SET @a = @b & @c;

### (Bitwise OR):

CREATE PROCEDURE testIntegerBitwiseOr @a INTEGER @b INTEGER @c INTEGER AS SET @a = @b | @c;

### (Bitwise Exclusive OR):

CREATE PROCEDURE testIntegerBitwiseXor @a INTEGER @b INTEGER @c INTEGER AS SET @a = @b ^ @c;

### (Bitwise NOT):

CREATE PROCEDURE testIntegerBitwiseNot @a INTEGER @b INTEGER @c INTEGER AS SET @a = ~@b:

### <> (Does Not Equal):

CREATE PROCEDURE testNotEqual @a INTEGER @b INTEGER

AS

SET @c = @a <> @b

SET @d = CASE @c

WHEN TRUE THEN @ a

ELSE @ b

END

### Boolean Keyword TRUE:

CREATE PROCEDURE testBooleanLiteral1 @a BOOLEAN

AS

SET @a = TRUE

### OR:

CREATE PROCEDURE testBooleanLocal @a BOOLEAN @b BOOLEAN @c BOOLEAN

AS

/* Just testing out how well multiline

comments are lexed out */

DECLARE @local BOOLEAN;

SET @local = @b OR (CASE 1/0 WHEN 100 THEN @c ELSE @c END);

SET @a = @local;

### NOT:

CREATE PROCEDURE testSimpleIfNot @a INTEGER @b BOOLEAN

AS

IF NOT @b

SET @a = 1

ELSE

SET @a = 2