Is there a way to select a database from a variable?

Question

Is there a way to select a database from a variable?

Declare @bob as varchar(50);
Set @bob = 'SweetDB';
GO
USE @bob

Kurdish

Answer 1

Unfortunately, no.

Unless you can execute the rest of your batch as dynamic SQL.

Using execute to dynamically execute SQL will change the context for the scope of the execute statement, but will not leave a lasting effect on the scope you execute the execute statement from.

In other words, this:

DECLARE @db VARCHAR(100)
SET @db = 'SweetDB'
EXECUTE('use ' + @db)

Will not set the current database permanently, but if you altered the above code like this:

DECLARE @db VARCHAR(100)
SET @db = 'SweetDB'
EXECUTE('use ' + @db + ';select * from sysobjects')
select * from sysobjects

Then the result of those two queries will be different (assuming you're not in SweetDB already), since the first select, executed inside execute is executing in SweetDB, but the second isn't.

Canaigre

Answer 2

declare @NewDB varchar(50)
set @NewDB = 'NewDB'
execute('use ' + @NewDB)

Domineca

As per the documentation (msdn.microsoft.com/en-us/library/ms188332.aspx), "Changes in database context last only until the end of the EXECUTE statement.", so this code is documented as not giving the intended effect. – Canaigre

Answer 3

#TempTables will presist across GOs

you can create the table in the first batch, insert/select data as necessary in that or any following batch.

here is some sample syntax:

CREATE TABLE #YourTableName
(
     col1   int         not null   primary key   identity(1,1)
    ,col2   varchar(10)
)

Swineherd