Ad

Variable Which Replace DB Of Name In SSMS

- 1 answer

I have two DB (DB1 & DB2) on the same server (instance) and I know i can write query like this:

select d1.name, d2.email
from [db1].[dbo].[person] as d1
join [db2].[dbo].[details_person] as d2
on d1.id = d2.id_p

but my question is: can i create something like variable which allows replace [db2] in my query when my database will change name.

I mean code similar like this: [$(var_db2)].[dbo].[details_person] but i don't know if i can create variable in SSMS which will be referes to database in the same server and if it is possible at all.

Ad

Answer

Use Dynamic Sql to set DB dynamically. Try something like this

declare @char varchar(10) = 'db_name'

exec 
(
'use '+Quotename(@char) + '
select d1.name, d2.email
from [dbo].[person] as d1
join [dbo].[details_person] as d2
on d1.id = d2.id_p'
)

or

DECLARE @sql NVARCHAR(max)
DECLARE @char VARCHAR(10) = 'db_rd'

SET @sql = 'select d1.name, d2.email
from ' + Quotename(@char) + '.[dbo].[person] as d1
join '+ Quotename(@char) + '.[dbo].[details_person] as d2
on d1.id = d2.id_p'

EXEC Sp_executesql @sql 
Ad
source: stackoverflow.com
Ad