How to get the result set from the Select statement from different databases having same table structure using cursor

Question

Below I've the piece of code where I need to get the result from the same piece of select statement from the different databases. I've used the cursor to select the database list from MASTER.dbo.sysdatabases and passed the same to variable( i.e. @name) which will hold the database name one after the other.

Under the select statement I've passed the same like @name.dbo.currencyconversionrates

However, while running the code I'm not getting the result. Error Message:-

Msg 102, Level 15, State 1, Line 23 Incorrect syntax near '.'.

My Code:

DECLARE @name nVARCHAR(100)
DECLARE @DBV nVARCHAR(100)


DECLARE @db_cursor CURSOR 
SET @db_cursor = CURSOR FAST_FORWARD
FOR 

SELECT name 
FROM MASTER.dbo.sysdatabases 
WHERE name  IN ('ENT_Benteler','ENT_DEUTSCHE_BANK','ELV_Henry_Schein','ENT_HONDA') 

OPEN @db_cursor   

FETCH NEXT FROM @db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN 
set @DBV= @name
FETCH NEXT FROM @db_cursor INTO @name 
--print @DBV
   --use  @name
   select row_id, base_currency,conversion_currency, effective_period_start, effective_period_end, rate, effective_period_start as 'Use this for Script' from @name.dbo.currencyconversionrates 
   order by CONVERSION_CURRENCY,EFFECTIVE_PERIOD_START

END
CLOSE @db_cursor   
DEALLOCATE @db_cursor

Please help me by rectify my issue.

Answer 1

select row_id, base_currency,conversion_currency, effective_period_start, effective_period_end, rate, effective_period_start as 'Use this for Script' 
from @name.dbo.currencyconversionrates 
order by CONVERSION_CURRENCY,EFFECTIVE_PERIOD_START

The above part is not correct because that parameters after from is not supported. You should utilize dynamic SQL, ignore SQL injection, you could do like this:

DECLARE @SQL VARCHAR(MAX)
SET @SQL = '
        select row_id, base_currency,conversion_currency, effective_period_start, effective_period_end, rate, effective_period_start as [Use this for Script] 
        from ['+ @name +'].dbo.currencyconversionrates 
        order by CONVERSION_CURRENCY,EFFECTIVE_PERIOD_START'
print (@SQL)
--EXEC (@SQL)

Try to print before you comment in EXEC