How to Select from a database using a dynamic variable

Question

I have a SQL 2008 database that is stored on the same instance, but this database is created by the user and name is stored in SQL table. How do I write a select statement using dynamic sql or is there a another way

So for example:

Main database - myDB
User database - userDB (this is stored in a myDB.dbo.tblUserDatabase)
userDB has a table called tblUserReports

I want to write something like this in dynamic sql:

SELECT * FROM userDB.dbo.tblUserReports

So tried:

declare @dbUser varchar(50)
set @dbUser = (SELECT strDBName FROM myDB.dbo.tblUserDatabase)

SELECT * FROM @dbUser.dbo.tblUserReports

sam yi · Accepted Answer · 2013-10-04 14:52:18Z

2

You can do this... dynamic sql can become unmanageable very quickly so be careful.

declare @dbUser varchar(50)
set @dbUser = (SELECT strDBName FROM myDB.dbo.tblUserDatabase)

DECLARE @sql NVARCHAR(1000)
SET @sql = 'SELECT * FROM ' + QUOTENAME(@dbUser) + '.dbo.tblUserReports'

EXEC sp_executesql @sql

edited Oct 4, 2013 at 14:52

answered Oct 4, 2013 at 14:39

sam yi

4,9451 gold badge33 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aaron Bertrand Over a year ago

Use QUOTENAME(@dbUser) for a little added protection.

LoztInSpace · Accepted Answer · 2013-10-04 14:30:27Z

1

You cannot parameterise the table name. You will have to use dynamic SQL in your client or stored procedures. It's a very unusual thing to want to do so think long & hard about if this is a good design. Maybe if you share what you are doing then you'll get some additional ideas as to how to approach your problem.

answered Oct 4, 2013 at 14:30

LoztInSpace

5,6971 gold badge17 silver badges29 bronze badges

Collectives™ on Stack Overflow

How to Select from a database using a dynamic variable

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related