How to use DB name as variable in a Query in Sql Server Stored Procedure

I am using 2 databases in SQL Server. Based on 1 query get Master DB name from a DB table. Using that Master DB table get values . Following is the SQL I used. I stored variable of DB name and I am not sure how to use it in 2nd query. Please help.


declare @dbname varchar(20);
declare @sql varchar(1000);
select @dbname = Databasename from ParentTable Where DBaseID = 1
      go
      set @sql = "Select B.Id, B.Name from tbl A JOIN @dbname..tbl1 B on A.CID = B.CID"
      go
      execute sp_executesql @sql
LVL 2
Shanmugam RajagopalAsked:
Who is Participating?
 
Shanmugam RajagopalConnect With a Mentor Author Commented:
This is the answer I found after trying multiple ways:

set @sql = 'Select B.Id, B.Name from tbl A JOIN ' + @dbname + '..tbl1 B on A.CID = B.CID'

Its single quote which solves the issue.
0
 
Éric MoreauSenior .Net ConsultantCommented:
you can try this:
set @sql = "Select B.Id, B.Name from tbl A JOIN " + @dbname + "..tbl1 B on A.CID = B.CID"

Open in new window

0
 
Shanmugam RajagopalAuthor Commented:
It didn't work
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Éric MoreauSenior .Net ConsultantCommented:
what do you see if you do
select @sql

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Get rid of the GOs:
declare @dbname varchar(20);
declare @sql varchar(1000);
select @dbname = Databasename from ParentTable Where DBaseID = 1
set @sql = "Select B.Id, B.Name from tbl A JOIN " + @dbname + "..tbl1 B on A.CID = B.CID"
execute sp_executesql @sql 

Open in new window

1
 
Éric MoreauSenior .Net ConsultantCommented:
Vitor is right, the GO statements can't be there!
0
 
Shanmugam RajagopalAuthor Commented:
Thanks for your help folks
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you put back the GO it won't work, right?
0
 
Shanmugam RajagopalAuthor Commented:
I added Go just for sample. The original problem is using variable of another select statement in 2nd sql. That is the subject of this issue too.

Thanks for following up
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
vidhyashan, don't forget to mark one or more comments as solution to have this question closed.
Cheers
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Recommendation to close this question by accepting the above comment as solution.
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.