sql server programming: I am trying to pass a value to a stored procedure, to the USE statement

DECLARE
@DATABASENAME VARCHAR(254)

SET @DATABASENAME = 'SalesPro'
USE @DATABASENAME

But on the line USE @DATABASENAME I receive the below error:

Incorrect syntax near '@DATABASENAME'.

Any ideas?

Thank you

--

Peace in Christ
Marco Napoli
http://www.ourlovingmother.org

Marco,

It's not possible to use a variable for the USE command. You can wrap it in
a dynamic sql statement but you must wrap all the other commands you wish t
do against that db in the dynamic sql as well. The scope of the use
statement is limited to only the scope of the dynamic sql execution.

--

Andrew J. Kelly
SQL Server MVP


[quoted text, click to view]

Marco,

Briefly - you can't. You will get an error if you try to use even a
fixed "use" command inside a stored procedure or trigger, let alone
a variable one. You can do it using dynamic sql, but once execution
returns to the stored proc, the use command is out of scope, so you
are back in the original database. So you either have to use
dynamic SQL to do all the work as well as the db change
- perhaps selecting into a fixed database/table.

Or a lot of typing along these lines:

use pubs
go
create proc MyStoredProc
as
select * from Information_schema.tables
return 0

use Northwind
go
create proc MyStoredProc
as
select * from Information_schema.tables
return 0

go
create proc ChangingDBs ( @DBName varchar(30) )
as
if @DBName = 'Pubs'
exec Pubs..MyStoredProc
if @DBName = 'Northwind'
exec Northwind..MyStoredProc
return 0
go

exec ChangingDBs 'Pubs'
exec ChangingDBs 'Northwind'

Regards
AJ

[quoted text, click to view]