Data Transformation Engine Database Interface Designer Reference Guide

Chapter 8 - Using Stored Procedures Examples Using Stored Procedures
Database Interface Designer Reference Guide
123
Using the
DBLOOKUP function, an example of the syntax used to call
DoSalaryIncrease would be:
DBLOOKUP ("call DoSalaryIncrease(" + EmpID:.:PayrollFile + ",?,?/" +
Salary:.:PayrollFile + ",?)",
"AdminDb.mdq",
"HR_DB")
If EmpID has a value of SM01930 and Salary has a value of 42,750, the call
syntax would expand to:
call DoSalaryIncrease (SM01930, ?, ?/42750, ?)
The output from this call is a single text item in which each field is delimited by the
pipe character (|). In this example, there will be three fields in the output—one for
each question mark character (?) placeholder. If you do not want to use all of the
output parameters, you can use any other character in place of the question mark
character. For example, if you did not use the EffectiveDate (parm4), the call
could be changed to:
DBLOOKUP("call DoSalaryIncrease ("+ EmpID:.:PayrollFile + ",?,?/" +
Salary:.:PayrollFile + ",X)",
"AdminDb.mdq",
"HR_DB")
Once again, if EmpID has a value of SM01930 and Salary has a value of
42,750, the call syntax would expand to:
call DoSalaryIncrease (SM01930, ?, ?/42750, X)
In this example, the EffectiveDate (parm4) would not be returned.
Returning the Value from a Stored Function
If you want the value from a stored function, prefix the procedure or function
name with
?=. For example:
DBLOOKUP("call ?= MyFunction('cat') , "My.mdq" , "MyDB")