Dynamic USE statement

I am looking if anyone has any reason why the code below is a bad idea.

This challenge deals with a challenge I have encountered in several different places and circumstances. How to run the same query against identical table structures in multiple databases. This is a problem for many small ERP installations that attempt being multi-site. Each location will have matching tables and different DBs on the server. When it comes to reporting data collection typically a proc is deployed in each DB that is an exact copy. I have seen this with sites that have as many as 14 individual sites. The code proliferation and managing deployment can be very challenging. Recently, I came upon an idea that no one else has proposed, at least I have not found it, and although simplistic it seems to work. What I don't know is if I am missing something subtle or some technical reason why it is a bad idea.

The code:

DECLARE @i AS INT 
SET @i = 1

WHILE @i < 3
BEGIN
    IF @i = 1 USE [DB1]
    IF @i = 2 USE [DB2]

    INSERT INTO ThirdDB.dbo.ReportTable (field)
        SELECT TOP 1 SomeNumber 
        FROM CommonTable WITH (NOLOCK)

    SET @i = @i + 1
  END

My initial testing says it works.

I would suggest not using a loop. You are basically going to have to hardcode each database name anyway. A little dynamic sql makes this a whole lot less effort. Also, the NOLOCK hint is almost always a bad idea. And TOP without an order by is also a bad idea. I removed both of those from this sample code.

Here are a couple of ways you could leverage dynamic sql for this. The first is going to generate separate insert statements. The second will generate a single insert statement with UNION ALL.

declare @Prefix nvarchar(max) = 'INSERT INTO ThirdDB.dbo.ReportTable (field) SELECT MAX(SomeNumber) FROM '
    , @PostFix nvarchar(max) = '.dbo.CommonTable;'
    , @SQL nvarchar(max) = ''

select @SQL = @SQL + @Prefix + DBName + @PostFix
from
(
    values('DB1'),('DB2')
) Names(DBName)


select @SQL

--uncomment the line below when you are satisfied the query is correct
--exec sp_executesql @SQL

Or the second way.

declare @SQL nvarchar(max) = ''

select @SQL = @SQL + 'SELECT MAX(SomeNumber) From ' + Names.DBName + '.dbo.CommonTable union all '
from
(
    values('DB1'),('DB2')
) Names(DBName)


set @SQL = 'INSERT INTO ThirdDB.dbo.ReportTable (field) ' + left(@SQL, len(@SQL) - 10)

select @SQL

--uncomment the line below when you are satisfied the query is correct
--exec sp_executesql @SQL