Dynamically USE databasename

Question

Post reply

Dynamically USE databasename

Express12

SSCoach

Points: 18514
More actions
September 14, 2007 at 2:47 pm

#177475

I'd like to dynamically USE a database name based on the users selection from a drop down list. Example: User selects the DBName "Customer" from a drop down list and I feed this name to a variable. How can I get T-SQL to execute the USE statement dynamically here?
DECLARE @DBName VARCHAR(255)
Use @DBName --< this does not work
GO
SELECT * FROM Address -- < which resides in the CUSTOMER DB
(FYI, this will be executed in a Reporting Services 2005 dataset)

BT

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Dynamic SQL is the key. If you're hell-bent on issuing a USE statement then use something like this:

declare

@sql varchar(200)

declare

@dbname varchar(25)

select

@dbname=<database>

select @sql='USE '+@dbname+'; select * from address'

exec

(@sql)

If you're only issuing one SQL command - it's actually easier to use 3-part names instead

declare

@sql varchar(200)

declare

@dbname varchar(25)

select

@dbname=<database>

select @sql='select * from '+@dbname+ '.dbo.address'

exec

(@sql)

Performance won't be so great with these things - so don't do anything "too heavy" this way...

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Bob Fazio SSChampion Points: 10794 More actions · Answer 2

What are you using to get the dropdown? How long will you keep that database? Depending on what you are trying to accomplish T-SQL may not be where you want to do this.

Express12 SSCoach Points: 18514 More actions · Answer 3

Thx for the feedback. FYI, to render the contents of the DROP down list in Reporting Services, I use an underlying dataset with this SQL:

SELECT [NAME] FROM master..sysdatabases

WHERE [name] NOT IN ('master', 'distribution', 'model', 'tempdb', 'msdb', 'pubs', 'northwind')

This renders a list of my application DB's. Once the User selects "a" DBname from this list, I save this in a Reporting Services variable and pass to the second dataset.

BT

S. Kusen SSChampion Points: 10936 More actions · Answer 4

I would use master.sys.databases (sql 2005 system view) instead of master..sysdatabases which is only included for backward compatibility in sql 2005