Dynamic DB Selection

[stin]

I'm using SQL Server 2000 and am looking for a way to dynamically select which database to select from in a stored procedure.

I have 4 possible DBs to choose from and don't want to write 5 SPs: 1 to choose which DB and 1 for each DB, simply changing the [db].dbo.tableName, know what I mean.

I'm aware of the exec('SELECT * FROM [db].dbo.tableName') option, but I'm looking for a (hopefully) easier solution.

Something like SELECT * FROM @myDB.dbo.tableName (which doesn't work).

Anyone have any suggestions?

[Ranthalion]

I would use dynamic sql string. Just build the sql as a string and execute all within your stored procedure.

[exterminator]

As long as you have the permissions to connect to the different databases - you can switch the database context using the "USE" statement. http://msdn2.microsoft.com/en-us/library/ms188366.aspx Can't you?