Dynamic Database Name?

Posted on 2010-01-08
Last Modified: 2012-05-08
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
Question by:mattkovo
    LVL 75

    Accepted Solution

    exec ('Select * from'+ @db+'.dbo.stores' )
    LVL 60

    Assisted Solution

    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

    Author Comment

    I take it there is no simplier way.  You can't use:

    Use [mydatabase]

    inside a stored procedure either correct?
    LVL 60

    Expert Comment

    by:chapmandew simpler way.
    LVL 22

    Assisted Solution

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    This video discusses moving either the default database or any database to a new volume.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now