*RUN_STORED_PROCEDURE 
Allowed uses: By Commit
Used to trigger the execution of an SQL stored procedure. *RUN_STORED_PROCEDURE={stored procedure name}('{params}')
Example
*RUN_STORED_PROCEDURE=spEliminate(ACTUAL,'2001.JAN')
Parameters containing delimiter characters (such as 2001.JAN) should be passed between single quotation marks (such as '2001.JAN').
To pass multiple values in a single parameter, it may be appropriate to enclose them between single quotes. However, it is up to the stored procedure code to support the unpacking of that parameter into its individual members, if appropriate.
Example
*RUN_STORED_PROCEDURE=spCompare('2001.JAN,2001.FEB','ITALY,FRANCE')
Stored procedures must be written in their own commit section, that is, they cannot coexist with a WHEN/ENDWHEN structure, nor can be part of any MDX rules. On the other hand, multiple stored procedures can be executed from the same commit section.
Example
*RUN_STORED_PROCEDURE=FirstProcedure('%TIME_SET%')
*RUN_STORED_PROCEDURE=SecondProcedure('%TIME_SET%')
*COMMIT
[account].[#SALES]=Units * Price
*COMMIT
The instruction RUN_STORED_PROCEDURE supports the keyword %APP% as the current application name. This is a common requirement for stored procedures that need to work on different applications.
Support of a log table in a stored procedure
The parameters passed to a stored procedure can include the name of a log table that the stored procedure can fill with whatever information is appropriate. The name of the table is generated automatically by the rules engine and passed to the stored procedure, once the table has been successfully created. This table contains a single field named "MSG" that can be filled with any length of text (it is of type NTEXT).
Once the stored procedure has completed execution and has written messages in the log table, the Logic engine reads its content and merges it into the normal rules log file, then the table is automatically dropped.
To activate this feature, you must include in the list of parameters passed to the stored procedure the keyword %LOGTABLE%. The rules engine replaces it with the appropriate table name.
Example
*RUN_STORED_PROCEDURE=spEliminate('%APP%','ACTUAL', '2001.JAN', '%LOGTABLE%')
Support of blank parameters passed to a stored procedure
A stored procedure fails if one of the required parameters is blank (null string). This situation may occur, for example, if one of the parameters is a set of members that was left blank in a DTS prompt, indicating that ALL members be processed.
The rules engine automatically traps this situation by replacing any null parameter ('') with the <NULL> keyword ('<NULL>'). The stored procedure has to check for parameters with a value and take the
appropriate action.
Example
This instruction:
*RUN_STORED_PROCEDURE=spEliminate( '', '2001.JAN')
is converted into:
*RUN_STORED_PROCEDURE=spEliminate( '<NULL>', '2001.JAN')
where <NULL> must be interpreted by the stored procedure as (for example) ALL categories.
Passing the selected region to a stored procedure using a table
The parameters passed to a stored procedure can include the name of a temporary table that the logic engine uses to store the members of the selected region for which it was run. The name of the table is generated automatically by the rules engine and passed to the stored procedure, once the table has been successfully created and populated with the appropriate information. This table contains two fields named DIMENSION and MEMBER, respectively, and is populated by the rules engine with one record per dimension/member combination that has been passed to it by the calling program.
Once the stored procedure has completed execution, the table is automatically dropped by the rules engine.
To activate this feature, you must include the keyword %SCOPETABLE% in the list of parameters passed to the stored procedure. The logic engine replaces it with the appropriate table name.
*RUN_STORED_PROCEDURE=spEliminate( [%SCOPETABLE%])