Database names in stored procedures

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 2000 Forums

Transact-SQL (2000)

Database names in stored procedures

Author

Topic

sjpaq@rogers.com
Starting Member

18 Posts

Posted - 2007-11-30 : 16:35:42

I am writing a stored procedure that resides in 1 database and I need to reference a table that lives in a different database in the same SQL server. When I refer to the table I use the database as the prefix. for example

select * from testormDB.TableName

This works fine.

Eventually though, I plan to move the SP that contains the above line from our test environment to our live environment at which point the statement above will need to be changed to

select * from liveormDB.TableName

Is there any way to use a symbolic variable of some sort that indicates the database name?
For example
select * from $env.TableName

Can this variable be passed in to the SP?

Any other smart ideas.

Thanks in advance

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-11-30 : 16:58:28

You could use dynamic sql but thats totally a different beast. You might want to rename your testdb the same as live db and modify your procs and then deploy. You can find the procs that reference the livefromdb by querying syscomments and doing a like search, though its all manual work.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-30 : 17:49:40

The best way to do it is to create views in your database that reference the tables in the other database. You can then write the stored procedures to use the views, and they never have to change.

When you change the database, just recreate the views. I find it handy to create a stored procedure that creates the views that takes database name as a parameter. Then, the only thing you have to do is execute one stored procedure to recreate the views.

CODO ERGO SUM

mbskier12
Starting Member

7 Posts

Posted - 2007-12-04 : 10:27:20

In the design of your stored procedure, declare a variable @DBName that you can pass in. When you make the call to the proc you can designate when you make your changes. I am assuming you are using a DTS package to make the call.

You may have to enclose the statement in your proc in an Exec statement:

Exec('Select * from '+@DBName+'.dbo.TableName')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources