• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 724
  • Last Modified:

How to use variable with use statement

Hello,

I have a script that I'm working on that uses a variable referencing the
database name (@DBName).  When I try to use this in the form of  "USE
@DBName", it doesn't work.  If I use an nvarchar datatype and create a
string (like the following);

DECLARE @SQL nvarchar(2000)
DECLARE @DBName nvarchar(200)

SET @DBName='TestDatabase'

SET @SQL = 'USE ' + @DBName
EXEC sp_sqlexec @SQL

The script runs, but the script remains focused on the master.

Is there any way to specify USE with a variable?

Thanks!
Rick
0
RIVAGHI
Asked:
RIVAGHI
5 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
the dynamic sql has a different scope, and the scope come back to the current db; so whatever you need to accomplish to be done on  the other db, you should put those script inside that @sql variable and run it
0
 
RiteshShahCommented:
you can't change focus but yes, you can do your task as Aneesh said, have a look at following script.
 though you are in MASTER database but can get results of Adventureworks database.


USE MASTER
go
 
DECLARE @SQL nvarchar(2000)
DECLARE @DBName nvarchar(200)
 
SET @DBName='ADVENTUREWORK'
 
SET @SQL = 'USE ' + @DBName + ';'
SET @SQL = 'SELECT * FROM SYS.OBJECTS'
EXEC sp_sqlexec @SQL

Open in new window

0
 
SharathData EngineerCommented:
In Ritesh code, you can try like this. Slightly modified his code for your requirement.

USE MASTER
go
 
DECLARE @SQL nvarchar(2000)
DECLARE @DBName nvarchar(200)
 
SET @DBName='AdventureWorks'
SET @SQL = 'USE ' + @DBName + ' SELECT * FROM SYS.Objects'
EXEC sp_sqlexec @SQL
 
You can also try with three part name.

USE MASTER
go
 
DECLARE @SQL nvarchar(2000)
DECLARE @DBName nvarchar(200)
 
SET @DBName='AdventureWorks'
SET @SQL = ' SELECT * FROM + @DBName + '.SYS.Objects'
EXEC sp_sqlexec @SQL
 
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
pcelbaCommented:
All the above written by Sharath is correct. If you really need to swith the database then you have to do it explicitly. Let suppose your database count is not infinite, so the only solution is:


DECLARE @DBName nvarchar(200)
SET @DBName='YourDatabase'
 
IF @DBName = 'Master'
   USE Master
ELSE IF @DBName = 'Model'
   USE Model
ELSE IF @DBName = 'YourDatabase'
   USE YourDatabase
ELSE
   PRINT "Invalid Database name"
 
-- Remember the USE statement is not allowed in stored procedures, functions, and triggers

Open in new window

0
 
Anthony PerkinsCommented:
You can also preface all objects with the database name and owner, that way you do not have to change databases.  This also means you would have to enable cross database ownership chaining.
0
 
RIVAGHIAuthor Commented:
Thank you all !!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now