Changing Database Context in T-SQL

How can one change the database context within a stored procedure? In other
words, something like this ...
Declare @.DB varchar(20)
set @.DB = (Select databasename from databases where activedatabase = 1)
Use @.DB
Select * from Table1
This code doesn't work, but I'm sure there's a way to do this.
Thanks for the help.
Ross> Use @.DB
> Select * from Table1
> This code doesn't work, but I'm sure there's a way to do this.
Sure, you can use dynamic SQL, blecch.
EXEC('SELECT <column_list> FROM '+@.DB+'.dbo.Table1')
Don't use SELECT * in production code.|||Ok, is there another option?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uWfLR9PnFHA.3448@.TK2MSFTNGP12.phx.gbl...
> Sure, you can use dynamic SQL, blecch.
> EXEC('SELECT <column_list> FROM '+@.DB+'.dbo.Table1')
> Don't use SELECT * in production code.
>|||Why?
Can't you use four-part names for objects?
ML|||USE takes a literal string for the database name. In your case, you would
have to use dynamic query to for further processing.
So, the answer is 'no'.
-oj
"Ross Culver" <rculver@.alliant-solutions.com> wrote in message
news:Ok23XBQnFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Ok, is there another option?
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:uWfLR9PnFHA.3448@.TK2MSFTNGP12.phx.gbl...
>|||What are you trying to accomplish? Most likely, you need to do something
like:
databasename.owner.tablename
SQL code stored in SQL Server is not really made to do dynamic stuff like
this. T-SQL is a super duper language for querying the database, but lousy
for doing much string manipulation. If you want to do stuff like this, use
your middle tier objects where it is far easier to build robust string
building facilities.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Ross Culver" <rculver@.alliant-solutions.com> wrote in message
news:O$TAu4PnFHA.3120@.TK2MSFTNGP09.phx.gbl...
> How can one change the database context within a stored procedure? In
> other words, something like this ...
> Declare @.DB varchar(20)
> set @.DB = (Select databasename from databases where activedatabase = 1)
> Use @.DB
> Select * from Table1
> This code doesn't work, but I'm sure there's a way to do this.
> Thanks for the help.
> Ross
>