T-SQL can use a variable in a select statement to specify a database

Question

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

DECLARE @source_database varvhar(200) SELECT @source_database = 'wibble' SELECT * FROM SELECT @source_database.dbo.mytable 

Answer 1

Not. I'm afraid not.

To use a variable for the database or column name, you must use dynamic sql.

Answer 2

For stored procedures only, without using a linked server or dynamic SQL

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

Answer 3

There is another (not necessarily beautiful) alternative:

 IF (@source_database = 'wibble') USE wibble; ELSE IF (@source_database = 'wibble2') USE wibble2; ELSE RAISERROR(....) SELECT * FROM dbo.myTable 

If you have a real number of databases, this can be tedious. But this is an option nonetheless.