Shanmugam Rajagopal
asked on
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
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
ASKER
It didn't work
what do you see if you do
select @sql
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
Vitor is right, the GO statements can't be there!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help folks
If you put back the GO it won't work, right?
ASKER
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
Thanks for following up
vidhyashan, don't forget to mark one or more comments as solution to have this question closed.
Cheers
Cheers
Recommendation to close this question by accepting the above comment as solution.
Open in new window