In SQL Server 2008 (Express), I have a stored procedure that includes something like this
SELECT * FROM DatabaseA.dbo.Table
I want to be able to do this instead
SELECT * FROM @MyDB.dbo.Table
where @MyDB is a parameter or a variable whose value is DatabaseA
I know I can use Dynamic SQL to construct the select statement as a string and then execute the string. But that strikes me as a kludge and in any event is pretty awkward when I try to take a large procedure and generalize it to operate on a database that's selected at run time.
So, any way to do that? I know SQL Server 2008 has the notion of a table variable but I haven't seen anything that corresponds to a database variable. Any ideas?