sql use statement with variable

sql-server sql-server-2005 dynamic-sql
Drejc picture Drejc · Jun 24, 2009 · Viewed 100.7k times · Source

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

Joel Goodwin picture Joel Goodwin · Jun 24, 2009

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

Related questions

Declare Variable for a Query String

I was wondering if there was a way to do this in MS SQL Server 2005: DECLARE @theDate varchar(60) SET @theDate = '''2010-01-01'' AND ''2010-08-31 23:59:59''' SELECT AdministratorCode, SUM(Total) as theTotal, SUM(WOD.Quantity) as theQty, …

Read More
Getting result of dynamic SQL into a variable for sql-server

Executing dynamic SQL as follows in Stored Procedure: DECLARE @sqlCommand nvarchar(1000) DECLARE @city varchar(75) SET @city = 'London' SET @sqlCommand = 'SELECT COUNT(*) FROM customers WHERE City = @city' EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city How do I use …

Read More
Dynamic SQL to generate column names?

I have a query where I'm trying pivot row values into column names and currently I'm using SUM(Case...) As 'ColumnName' statements, like so: SELECT SKU1, SUM(Case When Sku2=157 Then Quantity Else 0 End) As '157', SUM(Case When …

Read More