acess dynamic DB in StoreProedure ?

Question

hi all,

i use a sql server 2008

generally we access another DB in procedure like that

use DB1

go

select * from DB2.dbo.tbl1

but now we need to acces dynamically like that

select * from  dynamicDb.tbl1

i know my syntax is wrong but we need to something like that

pls help me because i create another DB with same structure but Diffrent DB name

thanks

anurag sharma

Answer 1

Use dynamic SQL, but read Erland's article before:

DECLARE @DATABASE_MARKER NVARCHAR(255) = N'@DATABASE@';

DECLARE @Current NVARCHAR(MAX);
DECLARE @Template NVARCHAR(MAX) = N'
  SELECT  * 
  FROM    @DATABASE@.tbl1;
';

SET @Current = REPLACE(@Template, @DATABASE_MARKER, 'DB1');
EXECUTE @Current;

SET @Current = REPLACE(@Template, @DATABASE_MARKER, 'DB2');
EXECUTE @Current;