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?

Sheepfold

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;

Ulema

didn't work for this: EXECUTE sp_executesql N'update @sourceDatabase.dbo.PredictedPrices set MinPrice = MinPrice' – Sheepfold

@Sheepfold - You need to concatenate into the string. See my answer for an example. – Buzzard

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;

Buzzard

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 ...

Foreworn