Dynamic SQL queries with TI parameters

It is possible to use parameters in the SQL statement of Turbo Integrator to produce dynamic ODBC queries

Here is how to proceed:

1. create your TI process
2. Advanced->Parameters Tab, insert parameter p0
3. Advanced->Prolog Tab add the processing code to define parameter p0
example: p0 = CellGetS(cube,dim1,dim2...)
4. save the TI process
5. open Data Source, add parameter p0 in WHERE clause
example select * from lib.table where name = '?p0?'
DO NOT CLICK ON THE VARIABLES TAB AT ANY TIME
6. run, answer "keep all variables" when prompted

If you need numeric parameters, there is a twist! numeric parameters do not work! (at least for TM1 9.x)
example: select * from lib.customer where purchase > ?p0? will fail although p0 is defined as a numeric and quotes have been removed accordingly.

But fear not, there is a "simple" workaround
1. proceed as above up to step 5
2. Advanced->Prolog tab, at the bottom: strp0 = NumberToString(p0);
3. Data Source tab, in the SQL statement replace ?p0? with CAST('?strp0?' as int)
example: select * from lib.customer where purchase > ?p0?
becomes select * from lib.customer where purchase > CAST('?strp0?' as int)
clicking the preview button will not show anything but the process will work as you can verify by placing an asciioutput in the Advanced->Data tab.
The CAST function is standard SQL so that should be working for any type of SQL server.

Note from Norman Bobo:

It is not necessary to add the "CAST" function to the SQL to pass numeric values. The trick is to think of the parameter as a substitution string, not a value. Define the parameter as a string and insert it into the SQL embedded in ?'s. When setting the value in the Advanced Prolog tab, convert the numeric value you would like to use into a string value. That value will be simply substituted into the SQL statement directly, as if you typed the value into the SQL yourself.

For instance:

In the Advanced/Prolog tab:

strFY = NUMBERTOSTRING(CELLGETN('Control Cube','FYElem','ControlValue');

Parameter:

Name: strFY, datatype: string, Default value: 2010, Prompt: Enter the FY:

In the SQL:
...
where FY = ?strFY?