[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

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
0
mattkovo
Asked:
mattkovo
3 Solutions
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now