Change table in a query with parameter

Skip to first unread message

Q&A Bot

Mar 21, 2021, 5:02:15 AMMar 21

I am trying to change table name based on a parameter.
I have two tables: table1 and table2
In my seektable report I have a dropdown with table names in parameters and want to run select from the one of the two tables
I tried it like this: select * from @my_param[{0}] but it puts table name inside quotes like this: select * from 'table1', which fails with error 

This happens because parameter values are passed in a safe way (escaped, to prevent SQL-injections). 

There are 2 variants how it is possible to change a table name in the SQL query:

1) if this is just a switch between 2 (hardcoded) tables you can define a parameter (this could be a dropdown with 2 items) with an "Expression" like that:
Parameter["my_param"] == "option1" ? 1 : null
 and then switch between tables with this parameter placeholder:
  select * from @my_param[table1;table2] 
here table1 will be used only if "my_param" is defined (not null), and table2 and in all other cases.

2)  paste parameter value 'as-is' as a part of SQL query with help of "Sql.Raw" function in the parameter's "Expression":
Sql.Raw( Parameter["my_param"] == "option1" ? "table1" : "table2" )
   "Select Query":
  select * from @my_param[{0}]  
  It is safe to use "Sql.Raw" if real parameter's value is resolved inside "Expression". Instead of the user's dropdown choice you can also switch between tables depending on the dimension(s) and/or measure(s) used in the report - for this purpose use Dimensions.Contains, Measures.Contains.
  Note that with "Sql.Raw" it is possible to insert user-entered parameter value into the SQL query 'as-is':
          Sql.Raw( Parameter["my_param"] ) 
 but you should be aware about the possibility of SQL injections: the end-user will be able to insert _any_ SQL into your query; this may be dangerous!

Reply all
Reply to author
0 new messages