跨服务器查询中动态指定数据库名进行关联是否可行？

当然可行，针对你这种需要根据当前客户信息动态指定关联数据库的场景，最直接的解决方式是动态SQL，以下是具体实现思路和示例：

核心思路 #

从Customers表中获取当前客户对应的数据库名称（假设Customers表有CustomerDBName字段存储客户独立库的名称），然后将这个名称拼接进SQL语句中，通过执行动态SQL完成关联查询。

示例代码 #

方法1：针对单个客户查询（推荐，支持参数化防注入） #

DECLARE @CustomerDBName NVARCHAR(128)
DECLARE @DynamicSQL NVARCHAR(MAX)
DECLARE @TargetCustomerID INT = 123 -- 替换为目标客户ID

-- 获取该客户对应的数据库名
SELECT @CustomerDBName = CustomerDBName 
FROM Customers 
WHERE CustomerID = @TargetCustomerID

-- 拼接动态SQL语句，用QUOTENAME处理数据库名避免语法错误
SET @DynamicSQL = N'
SELECT *
FROM Timeslips ts
INNER JOIN Invview IV ON IV.InvoiceID = ts.InvoiceID
INNER JOIN Customers C ON C.CustomerID = TS.CustomerID
LEFT JOIN ReceiptControl r ON r.InvoiceID = IV.InvoiceID
LEFT JOIN commissions c ON c.ReceiptControlKey = r.ReceiptControlKey
LEFT JOIN #temp T ON T.invoiceID = TS.InvoiceID 
    AND T.paymentdate = c.Date 
    AND T.damount = r.dAmount
LEFT JOIN [172.20.2.8].' + QUOTENAME(@CustomerDBName) + N'.dbo.order_commission OC 
    ON OC.orderid COLLATE SQL_Latin1_General_CP1_CI_AS = ts.OrderID
LEFT JOIN [172.20.2.8].' + QUOTENAME(@CustomerDBName) + N'.dbo.order_commission_type OCt 
    ON OCt.commissiontype COLLATE SQL_Latin1_General_CP1_CI_AS = OC.commissiontype
WHERE C.CustomerID = @TargetCustomerID'

-- 执行动态SQL并传入参数
EXEC sp_executesql @DynamicSQL, N'@TargetCustomerID INT', @TargetCustomerID = @TargetCustomerID

方法2：批量遍历所有客户查询 #

如果需要一次性拉取所有客户的关联数据，可以用游标遍历每个客户，拼接对应SQL执行：

DECLARE @CustomerDBName NVARCHAR(128)
DECLARE @CustomerID INT
DECLARE @DynamicSQL NVARCHAR(MAX)

-- 创建游标遍历所有客户
DECLARE CustomerCursor CURSOR FOR
SELECT CustomerID, CustomerDBName FROM Customers

OPEN CustomerCursor
FETCH NEXT FROM CustomerCursor INTO @CustomerID, @CustomerDBName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @DynamicSQL = N'
    SELECT 
        C.CustomerID,
        ts.InvoiceID,
        OC.orderid,
        OCt.commissiontype
        -- 按需添加其他字段
    FROM Timeslips ts
    INNER JOIN Invview IV ON IV.InvoiceID = ts.InvoiceID
    INNER JOIN Customers C ON C.CustomerID = TS.CustomerID
    LEFT JOIN ReceiptControl r ON r.InvoiceID = IV.InvoiceID
    LEFT JOIN commissions c ON c.ReceiptControlKey = r.ReceiptControlKey
    LEFT JOIN #temp T ON T.invoiceID = TS.InvoiceID 
        AND T.paymentdate = c.Date 
        AND T.damount = r.dAmount
    LEFT JOIN [172.20.2.8].' + QUOTENAME(@CustomerDBName) + N'.dbo.order_commission OC 
        ON OC.orderid COLLATE SQL_Latin1_General_CP1_CI_AS = ts.OrderID
    LEFT JOIN [172.20.2.8].' + QUOTENAME(@CustomerDBName) + N'.dbo.order_commission_type OCt 
        ON OCt.commissiontype COLLATE SQL_Latin1_General_CP1_CI_AS = OC.commissiontype
    WHERE C.CustomerID = ' + CAST(@CustomerID AS NVARCHAR(10))

    EXEC sp_executesql @DynamicSQL

    FETCH NEXT FROM CustomerCursor INTO @CustomerID, @CustomerDBName
END

CLOSE CustomerCursor
DEALLOCATE CustomerCursor

注意事项 #

• 必须用QUOTENAME()包裹数据库名称，避免因名称含特殊字符或关键字导致语法错误。
• 优先使用sp_executesql而非直接EXEC()，支持参数化查询，有效防止SQL注入风险。
• 确保执行查询的账号拥有远程服务器[172.20.2.8]上所有客户独立数据库的访问权限。

内容的提问来源于stack exchange，提问作者jin