How to use a varying database?

Question

I want to use a database which name is stored in a variable. How do I do this? I first thought this would work but it doesn't:

exec('use '+@db)

That will not change database context

Suggestions anyone?

Answer 1

The use statement is only in scope inside the exec block. Therefore you would have to do everything else in the same exec:

exec('use '+ @db + '
  --do other stuff'
) 

Answer 2

Unfortunately I don't know of a direct solution to this one. The nearest working version is:

DECLARE @db nvarchar(MAX)
SET @db = 'use DBname'
Exec sp_executesql @db

but this only changes the context for the length of the procedure call. However, more statements can be included in that call to make use of the context:

DECLARE @sql nvarchar(MAX)
SET @sql = 'use DBName SELECT * FROM Table1'
Exec sp_executesql @sql

Answer 3

Presumably you know all the possible database names. One (slightly inelligant) way of doing this would be to use a CASE or multiple IF statements to test the variable and hardcode the USE statement for each case.

Answer 4

If you absolutely have to do this using dynamic SQl, I prefer this:

DECLARE @sql nvarchar(MAX) 
declare @databasename varchar (20)
Set @databasename = mydatabase
SET @sql = 'SELECT * FROM ' + @databasename + 'dbo.Table1' 
Exec sp_executesql @sql 

The reason I prefer it is that you can extend it to use multipe datbases in the same query if need be.

I havea a concern that you don't know the datbase name for each table already without resorting to dynamic means. In other words, why can't you write:

SELECT * FROM mydatabase.dbo.Table1

If you have multiple databases with the same table names, likely you have a design problem.