T-SQL: can I use a variable as a database reference

Question

I want to do this:

update @sourceDatabase.dbo.PredictedPrices 

and then set @sourceDatabase as a variable.

But am I not allowed?

 Incorrect syntax near '.'. 

Is there another way?

Answer 1

For this you need to use SP_ExecuteSQL . i.e. dynamic query execution

Example:

 EXECUTE sp_executesql N'SELECT * FROM AdventureWorks2008R2.HumanResources.Employee WHERE BusinessEntityID = @level', N'@level tinyint', @level = 109; 

Answer 2

 DECLARE @Dynsql NVARCHAR(MAX) DECLARE @sourceDatabase sysname DECLARE @MinPrice MONEY SET @sourceDatabase = 'foo' SET @MinPrice = 1.00 SET @Dynsql = N'update ' + QUOTENAME(@sourceDatabase) + '.dbo.PredictedPrices set MinPrice = @MinPrice' EXECUTE sp_executesql @Dynsql, N'@MinPrice money', @MinPrice = @MinPrice; 

Answer 3

If you use this script in SSMS, you can use the SQLCMD mode (found in the Query menu) prior to the script variable for your database name:

 :setvar sourceDatabase YourDatabaseName update $(sourceDatabase).dbo.PredictedPrices set ...