How to use a different database?

Question

I want to use a database whose name is stored in a variable. How should I do it? At first I thought it would work, but it is not:

exec('use '+@db)

This will not change the database context.

Anyone suggestions?

Answer 1

Unfortunately, I do not know a direct solution to this problem. Nearest working version:

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

but this only changes the context of the length of the procedure call. However, you can include more operators in this call to use context:

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

Answer 2

If you need 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 

, , , , multipe , .

, . , :

SELECT * FROM mydatabase.dbo.Table1

, , .

Answer 3

use exec. exec:

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

Answer 4

. ( ) - CASE IF- hardcode USE .