CODEWITHSUNDEEP
sql-servert-sql
Andrew
Andrew

Reputation: 5277

T-SQL is it possible to use a variable in a select statement to specify the database

Is there a way to do something like this without converting the sql to a string and calling exec

DECLARE @source_database varvhar(200)
SELECT @source_database = 'wibble'

SELECT * FROM SELECT @source_database.dbo.mytable

Upvotes: 6

Views: 170

Answers (3)

AdaTheDev
AdaTheDev

Reputation: 147224

There is another (not necessarily pretty) alternative:

IF (@source_database = 'wibble')
    USE wibble;
ELSE IF (@source_database = 'wibble2')
    USE wibble2;
ELSE
    RAISERROR(....)

SELECT * FROM dbo.myTable

If you have any real number of databases, this may be tiresome. But it's an option nonetheless.

Upvotes: 0

gbn
gbn

Reputation: 432180

Only for stored procs without using linked server or dynamic SQL

DECLARE @myProc  varchar(200)
SELECT @myProc  = 'wibble.dbo.foobar'

EXEC @myProc

Upvotes: 2

Mitch Wheat
Mitch Wheat

Reputation: 300489

No. I'm afraid not.

It is necessary to use dynamic sql in order to use a variable for either a database or column name.

Upvotes: 3

Related Questions