Workflow Soluiton

Hey,

i have an Problem. I Create an Integration Library of Type Query/Script Generator with the follow SQL Query:

select SessionID from @tabelle

So if i Click find Query Paramaters, it shows @tabelle is an Input Parameter, so thats all Correct. But if i click the "Test Query" Button on Point 4. I get a window for an Paramater Input of "tabelle" if i set the right TableName "ReportProcess" i get the Error Message:

An ADO.NET exception was thrown. Error Message: Must declare the table variable "@tabelle"

So but its very Important that i can set the tablename dynamically.

You need to use Dynamic SQL to use the Query Gen in this way (if it's even allowed). By dynamic SQL, I mean declaring a long string, setting the string equal to the SQL you want executed, and then execute it. This would be your SQL for this:

DECLARE @SQL VARCHAR(1000)

SET @SQL = 'SELECT blah FROM ' + @tablename

EXEC(@SQL)

Try using the SQL above... it should detect @SQL and @TABLENAME... if it lets you generate this, hook up @SQL to anything (we override it immediately) and @tablename to your table.

I think Query Gen may block this, though... I'd try it and see.

the problem with setting a table name dynamically is the data type output.  the data type will be different depending on the table layout of the table you run the query against.  if by some chance any possible table you'd use for this component will all have the exact same column or columns you want to return, then it may work, but you'll need to configure the parameters as such:

set the @table parameter to Quotes Type = None and uncheck the Db Parameter box.  again, as reecardo mentioned, this isn't a recommended action, but it may work if your variables are always exactly what they need to be.