Solved

Passing Database as Variable in Update Statement

Posted on 2011-02-22
7
361 Views
Last Modified: 2012-05-11
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'.
0
Comment
Question by:SaiLoveAll
  • 3
  • 3
7 Comments
 
LVL 8

Expert Comment

by:rushShah
ID: 34953872
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
 
LVL 32

Expert Comment

by:ewangoya
ID: 34953926

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
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
ID: 34953947
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Closing Comment

by:SaiLoveAll
ID: 34954587
Thanks a Lot. It worked
0
 

Author Comment

by:SaiLoveAll
ID: 34954835
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
 
LVL 32

Expert Comment

by:ewangoya
ID: 34954892

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
 

Author Comment

by:SaiLoveAll
ID: 34955571
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

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question