Tsql: can i use a variable as database reference

Question

Hi,

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

For this to be done 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're running this script in SSMS, you can use SQLCMD Mode (found under the Query menu) to script a variable for your database name:

:setvar sourceDatabase YourDatabaseName

update $(sourceDatabase).dbo.PredictedPrices
    set ...