sql use statement with variable

Question

I'm trying to switch the current database with a SQL statement. I have tried the following, but all attempts failed:

1. USE @DatabaseName
2. EXEC sp_sqlexec @Sql -- where @Sql = 'USE [' + @DatabaseName + ']'

To add a little more detail.

EDIT: I would like to perform several things on two separate database, where both are configured with a variable. Something like this:

USE Database1 SELECT * FROM Table1  USE Database2 SELECT * FROM Table2 

Answer 1

The problem with the former is that what you're doing is USE 'myDB' rather than USE myDB. you're passing a string; but USE is looking for an explicit reference.

The latter example works for me.

declare @sql varchar(20) select @sql = 'USE myDb' EXEC sp_sqlexec @Sql  -- also works select @sql = 'USE [myDb]' EXEC sp_sqlexec @Sql 

Answer 2

   exec sp_execsql @Sql 

The DB change only lasts for the time to complete @sql

http://blog.sqlauthority.com/2007/07/02/sql-server-2005-comparison-sp_executesql-vs-executeexec/