Passing Database as Variable in Update Statement

DECLARE @Databasename NameType
DECLARE @CurrentDB NameType

SELECT @Databasename = 'Dbase'
SELECT @CurrentDB = db_name()

UPDATE l_a
SET l_a.mrb_flag = l.mrb_flag
FROM [@CurrentDB].dbo.location_all l_a
INNER JOIN [@Databasename].dbo.location_all l ON l.loc = l_a.loc
Where l_a.loc ='Test1_sa'

Error : - Invalid object name '@CurrentDB.dbo.location_all'.
SaiLoveAllAsked:
Who is Participating?
 
Ephraim WangoyaConnect With a Mentor Commented:
need space after 1_a.loc
DECLARE @Databasename NameType
DECLARE @CurrentDB NameType
DECLARE @SQL varchar(max)

SELECT @Databasename = 'Dbase'
SELECT @CurrentDB = db_name()

set @SQL = 'UPDATE l_a '+ 
           'SET l_a.mrb_flag = l.mrb_flag ' +
           'FROM [' + @CurrentDB + '].dbo.location_all l_a ' +
           'INNER JOIN [' + @Databasename + '].dbo.location_all l ON l.loc = l_a.loc ' +
           'Where l_a.loc = ''' + 'Test1_sa'''
EXEC(@SQL)

Open in new window

0
 
rushShahCommented:
try this
DECLARE @Databasename NameType
DECLARE @CurrentDB NameType

SELECT @Databasename = 'Dbase'
SELECT @CurrentDB = db_name()

DECLARE @SQL VARCHAR(MAX)

SET @SQL = 'UPDATE l_a
SET l_a.mrb_flag = l.mrb_flag
FROM [' + @CurrentDB + '].dbo.location_all l_a
INNER JOIN [@Databasename].dbo.location_all l ON l.loc = l_a.loc
Where l_a.loc =''Test1_sa'''

EXEC (@SQL)

Open in new window

0
 
Ephraim WangoyaCommented:

You can not use a variable as a database name or table name in a from clause or join, you have to use dynamic sql in such a case
DECLARE @Databasename NameType
DECLARE @CurrentDB NameType
DECLARE @SQL varchar(max)

SELECT @Databasename = 'Dbase'
SELECT @CurrentDB = db_name()

set @SQL = 'UPDATE l_a '+ 
           'SET l_a.mrb_flag = l.mrb_flag ' +
           'FROM [' + @CurrentDB + '].dbo.location_all l_a ' +
           'INNER JOIN [' + @Databasename + '].dbo.location_all l ON l.loc = l_a.loc' +
           'Where l_a.loc = ''' + 'Test1_sa'''
EXEC(@SQL)

Open in new window

0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
SaiLoveAllAuthor Commented:
Thanks a Lot. It worked
0
 
SaiLoveAllAuthor Commented:
Hi -

I was trying to do the same in a trigger after Insert. Where I am gettting Incorrect Syntax near i.

Can you figure out what is wrong at i ?

set @SQL ='UPDATE l_a '+
           'SET l_a.mrb_flag = l.mrb_flag ' +
           'FROM [' + @CurrentDB + '].dbo.location_all l_a ' +
           'INNER JOIN [' + @Databasename + '].dbo.location_all l ON l.RowPointer = l_a.RowPointer ' +
           'INNER JOIN inserted i ON l_a.RowPointer = i.RowPointer'+
               'WHERE i.site_ref = l.site_ref and i.loc = l.loc'
EXEC(@SQL)
0
 
Ephraim WangoyaCommented:

need a space after rowpointer

set @SQL ='UPDATE l_a '+
           'SET l_a.mrb_flag = l.mrb_flag ' +
           'FROM [' + @CurrentDB + '].dbo.location_all l_a ' +
           'INNER JOIN [' + @Databasename + '].dbo.location_all l ON l.RowPointer = l_a.RowPointer ' +
           'INNER JOIN inserted i ON l_a.RowPointer = i.RowPointer  '+
           'WHERE i.site_ref = l.site_ref and i.loc = l.loc'
EXEC(@SQL)
0
 
SaiLoveAllAuthor Commented:
Now, It says :-

Invalid Object Name inserted.

But How can I Join without using Inserted , to get the Inserted row pulled in a After Insert Trigger.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.