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?'
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.