Databases Reference
In-Depth Information
Calling Stored Procedures from MDX
After registering an assembly with Analysis Services, you can call it from MDX expressions
and queries. Because stored procedures extend native MDX functions, you can use them
in any expression that allows an MDX function with the same return type. For example, if
you call a stored procedure from the axis definition of the
SELECT
statement, it should
return a
Set
object or another MDX object that you can convert to a
Set
object, such as a
Member
or
Tuple
. (For the rules of type conversions, see Chapter 11.) You can see an
example in the following query:
SELECT MyUserDefinedFunctionAsSet() ON COLUMNS from MyCube
NOTE
If the stored procedure returns a numeric or string expression, the axis definition
clause of a
SELECT
statement cannot use it.
A stored procedure that does not return a value (the return type is
void
) cannot be used as
part of an MDX expression or
SELECT
statement. You can use it only from a
CALL
state-
ment (provided by Analysis Services). For example, you can write a stored procedure that
performs a file copy operation and call it with the following expression:
CALL CopyFile(“sourcefile.txt”, “destfile.txt”)
The
CALL
statement executes outside the context of an MDX query. It is useful for
performing administrative tasks, rather than retrieving data. Like a stored procedure that
does not return any values, a
CALL
statement can execute stored procedures that return a
DataSet
object or an object that implements the
IDataReader
interface. In both cases,
Analysis Services uses the serialization interfaces of the .NET Framework to serialize the
value returned from the stored procedure.
Stored procedures can be referenced from MDX by name (with some limitations explained
later in this chapter) or by qualified name. If you deploy more than one assembly, it is
possible you could end up with more than one function with the same name, and that
can cause ambiguity.
NOTE
To avoid ambiguity, we strongly recommend using the qualified names of stored proce-
dures. As a bonus, if you use qualified names, you get faster name resolution.
The qualified name of a stored procedure consists of three components (assembly name,
class name or interface name, and method name) separated by either a period (.) or an
Search WWH ::
Custom Search