CODEWITHSUNDEEP
sql-server
Omer Anees
Omer Anees

Reputation: 133

MS SQL Server how to changing database name in same query?

I have two different databases in the remote location, which i use through a linked server, every time when i do search i need to look both of them by passing a id. so i am using following two queries. but i want to user only one by passing the db name as a variable.

select * from [LINKED_SERVER].db2_WorkDB.dbo.Tbl_Jobseeke with(nolock) where id = 12
select * from [LINKED_SERVER].db3_SalesDB.dbo.Tbl_Jobseeke with(nolock) where id = 12

Is there any why by which i can pass a variable for db name that will allow me to write a single query and can use both db one by one.

NOTE: i don't want to use a query as a string and them pass it to the EXEC.

Upvotes: 0

Views: 127

Answers (2)

Alexander Kulakov
Alexander Kulakov

Reputation: 11

Use dynamic SQL

declare @dbname SYSNAME, @query as NVARCHAR(MAX);

SET @dbname = 'your db name';
SET @query = 'SELECT FROM [YOUR SERVER].' + @dbname + '.dbo.Tbl_Jobseeke with(nolock) where id = 12';

EXEC sp_executesql @query;

Upvotes: 1

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56697

You could do with exec, but that's far from nice...

DECLARE @tblName NVARCHAR(20)
SET @tblName = 'db2_WorkDB'

exec('SELECT * FROM [LINKED_SERVER].' + @tblName + '.dbo.Tbl_Jobseeke with (nolock) where id = 12')

What would I do? I'd use a union:

select 'db2_WorkDB' as source, * from [LINKED_SERVER].db2_WorkDB.dbo.Tbl_Jobseeke with(nolock) where id = 12
union
select 'db3_SalesDB' as source, * from [LINKED_SERVER].db3_SalesDB.dbo.Tbl_Jobseeke with(nolock) where id = 12

If you want to include duplicate records, use union all.

Upvotes: 1

Related Questions