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

Question

I want to accomplish this:

update @sourceDatabase.dbo.PredictedPrices

and then set @sourceDatabase as a variable.

But I'm not allowed?

Incorrect syntax near '.'.

Is there another way?

Answer 1

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;