How to set a specific database inside a procedure

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

General SQL Server Forums

New to SQL Server Programming

How to set a specific database inside a procedure

Author

Topic

smeira
Starting Member

6 Posts

Posted - 2008-03-12 : 01:14:13

Hi,

I'm creating a generic procedure in the master db and I'm getting the database name as a input parameter.
How do I set the database inside the procedure , according to this parameter?
T-Sql does not accept "USE" inside procedures .
I tried "sp_defaultdb" but when I select from a specific table in the procedure it still does not identify the table as belonging to that database, it still looks for the table in the master db.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-12 : 01:20:22

use reference the table using the database name, schema, object name


select * from db2.dbo.table2

KH
[spoiler]Time is always against us[/spoiler]

smeira
Starting Member

6 Posts

Posted - 2008-03-12 : 02:03:52

It is not accepting db name as a variable in a cursor:
use AdventureWorks
declare @db nvarchar(50)
declare @v_table nvarchar(50)
set @db='AdventureWorks';
declare cursor c1 for
select name from @db.sys.tables;
open c1
fetch c1 into @v_table;
close c1;

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 02:30:12

http://www.sommarskog.se/dynamic_sql.html

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources