CODEWITHSUNDEEP
sqlsql-server
volume one
volume one

Reputation: 7563

How to select from unknown number of databases?

I want to show a customer a history of their total orders across multiple 'vendors'. Each vendor has a separate database in SQL server to store their own orders.

In my database I only know which vendors the user is signed up with. So my sequence needs to go like this:

  1. Get all the VendorIDs that the user is signed up with.
  2. Go to the Vendor table and get their server + database name
  3. Perform a select statement that gets all orders from each Order table in each of the Vendor databases that the user is signed up to.
DECLARE @UserID int = 999

SELECT Count(OrderNumber) AS 'Orders'

FROM
--- Need some kind of loop here?
[VendorServer].[VendorDB].[OrderTable] o1

WHERE 
o1.UserID = @UserID

How would I get the aggregate of the total number of orders this customer made when their orders are spread across multiple databases?

The User may be signed up to over 100 vendors. So it has to query across 100 databases. This is an extreme example but its possible.

Upvotes: 0

Views: 166

Answers (2)

Serpiton
Serpiton

Reputation: 3684

This can be solved using dynamic query: the query itself is generated dynamically and then executed.

Without the table schema it's impossible to write something that will work in your environment but the idea will be

DECLARE @query NVARCHAR(MAX) = ''

SELECT @query += 'UNION ALL 
                  SELECT whatever 
                  FROM   ' + VendorServer + '.' + VendorDB + '.OrdeTable
                  WHERE  condition'
FROM   Vendor
WHERE  VendorID IN (all the VendorIDs that the user is signed up with) 

SET @query = SUBSTRING(@query, 10, LEN(@query))

EXEC sp_executesql(@query)

The OP in a comment described this schema

CREATE TABLE User_Vendor (
  UserID int
, VendorID int
)

CREATE TABLE Vendors (
  VendorID int
, Name varchar(50)
, DatabaseName varchar(50)
, Servername varchar(50)
)

in that case the query/stored procedure body will be

DECLARE @UserID int = '999'
DECLARE @query NVARCHAR(MAX) = ''

DECLARE @vUserID nvarchar(10) = CAST(UserID as nvarchar(10))

SELECT @query += 'UNION ALL 
                  SELECT Count(OrderNumber) AS [Orders]
                  FROM   ' + v.Servername + '.' + v.DatabaseName + '.OrdeTable
                  WHERE  o1.UserID = ' + @UserID + ' '
FROM   User_Vendor uv
       INNER JOIN Vendors v ON uv.VendorID = v.VendorID
WHERE  uv.UserID = @UserID

SET @query = SUBSTRING(@query, 10, LEN(@query))

EXEC sp_executesql(@query)

SQLFiddle demo with a SELECT @query instead of the EXEC sp_executesql(@query)

the added variable @vUserID is to avoid multiple CAST in the query, the User table is not needed in the query.

To get the total figure of the orders, instead of the count for every vendor, the line

SET @query = SUBSTRING(@query, 10, LEN(@query))

should be changed to

SET @query = 'SELECT SUM([Orders]) [Orders]
              FROM (' + SUBSTRING(@query, 10, LEN(@query)) + ') a'

Upvotes: 1

Rex
Rex

Reputation: 2140

though similar to Serpiton's answer, just post for your reference - 

    DECLARE @userId INT;
    DECLARE @sql VARCHAR(MAX) = '';

    SELECT  @sql += ' SELECT COUNT(*) Cnt FROM ' + 
                    ServerName + '.' + DbName + '.' + tblNameWithSchema + 
                    ' WHERE UserId = ' + CAST(@userId AS VARCHAR(50)) + ' UNION ALL '
    FROM    yourTbl
    WHERE   UserId = @userId;

    -- Remove excessive 'Union All'
    SET @sql = SUBSTRING(@sql, LEN(@sql) - LEN(' UNION ALL '), LEN(' UNION ALL '));

    SET @sql = 'SELECT SUM(Cnt) FROM (' + @sql + ') tmp'

    EXECUTE sp_executesql(@sql);

Upvotes: 1

Related Questions