Dynamic Database Name?

Is it possible to use a dynamic database name in the select statement?  Here is what I'm trying to achieve.

Declare @db varchar(50);
Set @db = 'mydatabase'

Select *
from @db.dbo.stores
mattkovoAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
exec ('Select * from'+ @db+'.dbo.stores' )
0
 
chapmandewCommented:
dont use exec, use sp_executesql

Declare @db varchar(50), @x nvarchar(1000)
Set @db = 'mydatabase'


set @x = N'Select * from ' + @db + '.dbo.stores'
execute sp_executesql @x
0
 
mattkovoAuthor Commented:
I take it there is no simplier way.  You can't use:

Use [mydatabase]

inside a stored procedure either correct?
0
 
chapmandewCommented:
correct...no simpler way.
0
 
dportasCommented:
There are much better options. I think it's a bad idea to use a dynamic database name in T-SQL. In fact I dislike using database names at all in procedural code because it leads to tight-coupled code that is potentially harder to maintain. It can make dependency checking and impact analysis more difficult for example.

Some of the alternatives are:
- use a partitioned view and configure the database name(s) at install time.
- use synonyms
- use a server alias and default the database name on that connection.
- use a connection string which can be configured at runtime.
- consolidate your multiple databases and use filegroups and partitioned tables rather than separate databases.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.