Use Database statement with Variable Name

Question

I am trying to store a database name in a variable:

DECLARE @SQLString  varchar(max)
DECLARE @DBIn  varchar(50)

SET @DBIn ='myDB'
SET @SQLString = 'USE ' + @DBIn 

select @SQLString
EXEC(@SQLString)

There are no errors when I execute this code but it does not actually works (the database does not change as it would if I just run a USE [myDB]).

It would change in the scope of the dynamic sql, not outside of it — Lamak
– Lamak, Commented Feb 4, 2015 at 17:32

George · Accepted Answer · 2015-02-04 18:52:07Z

2

Why not just add database name to schema.table name?

DECLARE @SQLString  varchar(max)
DECLARE @DBIn  varchar(50)

SET @DBIn ='myDB'
SET @SQLString = 'SELECT * FROM ' + @DBIn + '.dbo.MyTable' 

select @SQLString
EXEC(@SQLString)

answered Feb 4, 2015 at 18:52

George

7025 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3566591 Over a year ago

George, I get the following error: "Invalid object name 'myDB.dbo.MyTable' "

George Over a year ago

That means you don't have table 'MyTable' in the database, I gave it just as example. I've re-read your question, it looks like you want to change database scope, but it is really not needed because you can query every object on same server by using <DBNAME>.<SCHEMANAME>.<OBJECTNAME>

user3566591 Over a year ago

You are correct. I think what is left is for me to change my username.. :) thanks

Swapnil · Accepted Answer · 2015-02-05 13:52:36Z

0

DB name is changed for the script which is included in @SQLString. For e.g, below code will work:

DECLARE @SQLString  varchar(max)
DECLARE @DBIn  varchar(50)

SET @DBIn ='myDB'
SET @SQLString = 'USE ' + @DBIn+ ' SELECT TOP 100 * FROM dbo.MyTable'

select @SQLString
EXEC(@SQLString)

answered Feb 5, 2015 at 13:52

Swapnil

4342 silver badges14 bronze badges

Collectives™ on Stack Overflow

Use Database statement with Variable Name

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related