CODEWITHSUNDEEP
sql-server-2005t-sql
Just a learner
Just a learner

Reputation: 28662

In SQL Server 2005, how can I use database_b, do something, then use the old db database_a in TSQL?

In SQL Server 2005, how can I use database_b, do something, then use the old db database_a in TSQL? The following is my code but there is some problem with it. Who can help me to identity the problem? Great thanks.

DECLARE @old_database_name VARCHAR(200)
SET @old_database_name = db_name()

use mydatabase

create table t1(id int identity(1,1))

use @old_database_name

Upvotes: 1

Views: 52

Answers (3)

Moe Sisko
Moe Sisko

Reputation: 12063

This is similar to AdaTheDev's "EXECUTE" example, but uses sp_executesql : 

-- Do stuff in current DB here

EXECUTE mydatabase..sp_executesql N'create table t1(id int identity(1,1));'

-- Do more stuff in current DB here. This context will not have changed since before the EXECUTE statement

Upvotes: 0

HLGEM
HLGEM

Reputation: 96658

Why are you even using USE statements for this?

create table mydatabase.dbo.t1(id int identity(1,1))

Upvotes: 0

AdaTheDev
AdaTheDev

Reputation: 147374

You'll need to use dynamic sql to do this.

e.g.

-- Do stuff in current DB here

EXECUTE ('USE mydatabase; create table t1(id int identity(1,1));')

-- Do more stuff in current DB here. This context will not have changed since before the EXECUTE statement

Upvotes: 2

Related Questions