locked
Problem with parameterized query. RRS feed

  • Question

  • I'm trying to use a parameterized query where the parameter is the table I'm querying from (i.e. "select * from @datatable).  Whenever I try to execute the report, I get an error stating that I "... must declare the table variable @datatable".  I think that's because my sql is not legal -- I haven't defined the variable to be an acutal table structure.  Is that correct?

    If so is there any other way to select from a table name that is dynamic using the paramteter structure that RDL supports?  I have been able to do what I want by making the value of the commandtext element an expression that evaluates to the proper sql.  Parameters help cut down on possible malicious SQL and I'm hoping to be able to make use of the supported parameter system in RS if possible.

    Any help is appreciated.

    Friday, July 29, 2005 2:02 PM

Answers

  • You can't substitute a table name using standard parameterization. Basically, you would either need to write some sort of stored proc that constructed the SQL and used an EXEC statement or you can use an expression string for the query.

    ="SELECT * FROM [" & Parameters!TableName.Value & "]"

    You need to be careful here about SQL injection. If someone passes in "FOO GO DROP DATABASE XXX" as the parameter, you are in for trouble. You would want to write some sort of function to vaildate the input.
    Sunday, August 14, 2005 11:03 PM