SQL Statement using Database name pulled from a select statement-CodePudding

Here is my scenario ParentDB has table with JournalDB Names (I have the select to return the latest JournalDB Name)

JournalDBName = select top 1 DBName from ParentDB.dbo.JournalNames order by ObjectID desc

Trying to run 2nd Select statement where I can "USE" the JournalDBName similar to

USE "JournalDBName"
select  
Data1,
Data2,
from JournalRecordLog

I'm stuck on passing JournalDBName for "USE" to the 2nd statement.

Any help is appreciated

CodePudding user response：

You need to use dynamic SQL:

DECLARE @db   sysname, 
        @exec nvarchar(1000),
        @sql  nvarchar(max);

SELECT TOP (1) @db = DBName 
  FROM ParentDB.dbo.JournalNames 
  ORDER BY ObjectID DESC;

SET @exec = QUOTENAME(@db)   N'.sys.sp_executesql';

SET @sql = N'SELECT Data1, Data2 FROM dbo.JournalRecordLog;';

EXEC @exec @sql;