dynamically changing USE database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

dynamically changing USE database

Author

Topic

nic
Posting Yak Master

209 Posts

Posted - 2007-04-06 : 17:00:40

Hi,
Is there an easy way to use a parameter with the USE statement? Essentially I have a script that needs to switch between two databases. It would be nice if I could set a parameter for the database name and therefore only have to set the values in one place. Something like this:


Declare
@prodDatabase varhcar(20)
,@testDatabase varchar(20)

SET @prodDatabase = 'MyProdDatabase'
SET @testDatabase = 'MyTestDatabase'

USE @prodDatabase
-- do prep stuff

USE @testDatabase
-- do stuff (like transfering data)
INSERT INTO @prodDatabase.dbo.Table SELECT * from TestTable

Use @prodDatabase
-- do clean up stuff

Sometimes need to provide script to clients and it would be safer if they could just modify the param value instead of updating the actual statements etc.

Thanks,
Nic

Nic

X002548
Not Just a Number

15586 Posts

Posted - 2007-04-06 : 17:03:30

first of all, once you use USE, you don't need to qualify the DB in the tables names

Secondly, if you know the db, it's only 2 lines.

What am I missing?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

cvraghu
Posting Yak Master

187 Posts

Posted - 2007-04-06 : 19:40:27

Create an SP with these two db names as parameters. Use dynamic sql within the SP to refer to objects of a specific db.

Declare
@prodDatabase varhcar(20)
,@testDatabase varchar(20)

SET @prodDatabase = 'MyProdDatabase'
SET @testDatabase = 'MyTestDatabase'

exec dostuff
@prodDatabase
,@testDatabase

create proc dostuff
@prodDatabase varhcar(20)
,@testDatabase varhcar(20)
as
begin

...

exec (INSERT INTO ' + @prodDatabase + '.dbo.Table SELECT * from ' + @testDatabase + '.dbo.TestTable')

end

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-07 : 02:59:31

You should avoid passing object names as parameter, as you dont have any option other than using Dynamic sql.
As said, if you know the db names then there is no need of passing them as parameters
Otherwise you will end up with complex Dynamic sql
To know more about it read www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-04-09 : 22:16:26

It looks like someone is trying to rewrite the copy database wizard... or, perhaps, the creation of "Gold Sets" for testing purposes. The dynamic SQL just might be worth it all...

--Jeff Moden

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-09 : 23:36:58

if you execute your script via sqlcmd.exe, you can pass the database on the cmd line in the -d param.

www.elsasoft.org

meenu84
Starting Member

1 Post

Posted - 2007-05-08 : 17:27:02

Hi , I have the exact same requirement as Nic,
I wonder if Nic found the answer he searched for or if he had to so to a solution based on the SP.
The reason for using DB names as parameters is I need to use the same DTS package in different environments, and in those environments the DB names changes but all other stuff in the script of the "Execute SQL task" querry remains the same.

Can Nic or any one help me here or is the use of a stored procedure the only answer??

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources