I alwasy wondered how one can do it. This is something that I had seen during my employment but as not involved, was not able to recall it. finally found how to do it. MicroStrategy kb search is very pathetic.

How to use it: Open VLDB Properties of the report and changed setting Pre/Post Statements based on your requirement.

Take an example: {call GenerateDate([Age Interval])}

Calling a stored precedure from MictoStrategy Report

{} curly brackets are required as per MicroStrategy.
MicroStrategy recommends using CALL statement instead of EXEC to execute/call a stored procedure.
[Age Interval] is a MicroStrategy numeric value prompt that can be passed as parameter to the stored procedure. This is optional.

Other things to do but not for all ODBC Drivers.

MicroStrategy ODBC Driver must be configured to accept results from stored procedures. As per tech-note I found only Oracle needs to do following.

UNIX: The following line must be added manually to the Data Source Name (DSN) definition in the ODBC.INI file
ProcedureRetResults=1
Windows: Open the DSN and see if there is some setting for Procedure Result. Select that.

This can be used to pass parameter to a stored procedure from MicroStrategy too. In cases where multiple values need to be passed from the same prompt, an element list prompt should be used.