CODEWITHSUNDEEP
sqlsql-server
Robert_Junior
Robert_Junior

Reputation: 1123

SQL Server: How to use a database name as a parameter in a stored procedure?

How can I do a join on tables of 2 different db in same domain like 

SET DBname = "sample"
SELECT *
FROM Table1
INNER JOIN DBname.Table2 T On Table1.key = T.Key

Its reporting like

Invalid column name T

Any help would be appreciated

Upvotes: 1

Views: 5164

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

You have to use dynamic sql:

@DBname = 'sample';
declare @sql nvarchar(max);

set @sql = '
SELECT *
FROM Table1 INNER JOIN
     @DBname.table2
     On Table1.key = Table2.Key';

set @sql = replace(@sql, '@DBname', @DBname);

exec sp_executesql @sql;

Databases in general -- and SQL Server in particular -- do not allow parameters to represent database names, schema names, table names, or column names.

Upvotes: 5

Related Questions