• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 62
  • Last Modified:

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
0
Shanmugam Rajagopal
Asked:
Shanmugam Rajagopal
  • 4
  • 4
  • 3
1 Solution
 
É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
 
Éric MoreauSenior .Net ConsultantCommented:
what do you see if you do
select @sql

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now