I've got a stored procedure that allows an IN parameter specify what database to use. I then use a pre-decided table in that database for a query. The problem I'm having is concatenating the table name to that database name within my queries. If T-SQL had an evaluate function I could do something like
eval(@dbname + 'MyTable')
Currently I'm stuck creating a string and then using exec() to run that string as a query. This is messy and I would rather not have to create a string. Is there a way I can evaluate a variable or string so I can do something like the following?
SELECT *
FROM eval(@dbname + 'MyTable')
I would like it to evaluate so it ends up appearing like this:
SELECT *
FROM myserver.mydatabase.dbo.MyTable
