Dynamic USE statement

Question

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.

It doesn't scale well. If you have a lot of databases you'll end up with a lot of IF statements. You could create a mapping table of values of i and corresponding databases and use it to create a dynamic SQL statement. — squillman, Dec 13 '19 at 20:01
Confusing to use 3 party names for the destination table but 2 part names for source table. Splattering nolock everywhere is not a good sign either. The better method is to use dynamic sql in some fashion and "find" (e.g., select from meta-data) the databases that should be used. Examples for running a query in all (or some subset) of databases in an instance are easy to find. — SMor, Dec 13 '19 at 20:20
And using TOP without an order by clause is generally a logic flaw. — SMor, Dec 13 '19 at 20:21
Could you use Powershell? Maybe something like [this](https://sqljana.wordpress.com/2017/06/20/sql-server-running-a-script-or-sql-on-multiple-instancesdatabases-with-powershell-level-100/)? — Isaac, Dec 13 '19 at 20:28
Take a look at sp_foreachdb: https://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/ — David Browne - Microsoft, Dec 13 '19 at 20:36
Thank you all for making excellent points. My apologies for any lack of clarity. — Paul Wichtendahl, Jan 02 '20 at 03:42
Thank you all for making excellent points. My apologies for any lack of clarity. It is not a "For all" dilemma as the list of possible DBs that would need to be called is a well constrained list. The select statements, as is common with financial reports can be hundreds of lines. The idea is to be able to have one version that all sites can call. The easiest answer would be to convert all of it to C# and WPF where the connection string can be dynamic within a single call. I did find out that if you compile a stored procedure the USE statement is not allowed. With good reason. — Paul Wichtendahl, Jan 02 '20 at 03:48

score 1 · Accepted Answer · answered Dec 13 '19 at 20:26

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

Greatly appreciate the effort Mr Lange. I did end up using a variation of several hints that also included yours. After taking on several revisions of what is said here I ended up making a custom version of the "ineachdb" suggested by Mr Browne above that afforded me the side benefit of additional logging and monitoring. Beyond that by using a "lookup table" as was also suggested I can use dissimilar DB functions across the server and get useful data. Side note: The lack of TOP and use of WITH (NOLOCK) is worthwhile for a public post. I was careless with cut and paste. my bad. — Paul Wichtendahl, Feb 23 '22 at 23:15

Dynamic USE statement

1 Answers1