sql - 链接服务器查询/动态 SQL

Question

我目前有一个链接服务器，我在存储过程中查询它。我的查询目前工作得很好，但是这个查询需要针对我拥有的每个代码分支进行更改。我想知道获取我在跨服务器查询中调用的数据库名称的最佳方法是什么。

例如:服务器 A 有到服务器 B 的链接。服务器 A 包含 3 个数据库。 SRV_A.DB1_DEV、SRV_A.DB2_Trunk、SRV_A.DB3_Prod 每个都链接到它们的服务器 B 对应项... SRV_B.DB1_DEV、SRV_B.DB2_Trunk、SRV_B.DB3_Prod

服务器 A 上的每个数据库都有相同的存储过程。存储过程中唯一发生变化的是跨服务器选择。所以 SRV_A.DB1_Dev 在 sproc 中有一个选择:

SELECT foo FROM [SRV_B].[DB1_DEV].[foo_table] WHERE bar = 1 

而主干分支上的存储过程是

SELECT foo FROM [SRV_B].[DB2_Trunk].[foo_table] WHERE bar = 1

因为我希望有一个 VS 项目将数据库部署到提到的每个分支，所以我希望能够动态填写数据库名称。我提出的有效解决方案是对 CHARINDEX 函数使用一系列 IF 检查，然后使用动态 SQL 创建查询，如下所示:

DECLARE @dSql NVARCHAR(4000);
DECLARE @databaseName NVARCHAR(100) = DB_NAME();
DECLARE @tableName NVARCHAR(100);
IF SELECT CHARINDEX('Dev', @databaseName, 0)
    SET @tableName = '[SRV_B].[DB1_DEV].[foo_table]
    ...Same if & set for Trunk
    ...Same if & set for Prod
SET @dSql = 'DECLARE @retID INT;SELECT foo FROM ' + @tableName 
+ ' WHERE bar = 1';SET @retID = SELECT SCOPE_IDENTITY()'
EXEC(@dSQL);

不过，我不得不想象有更好的解决方案，如果有人可以帮助我，我将不胜感激。如果通过一些外部拍摄，这是最好的方式，也请告诉我。

谢谢，詹姆斯

Answer 1

解决此问题的一种方法可能是通过将链接服务器名称包装在 synonym 中来抽象链接服务器名称。 :
请注意目标表名称中的额外部分 - 跨服务器查询需要一个由四部分组成的名称 - 我假设这是问题中的错字并且 foo_table 在 dbo 模式

CREATE SYNONYM dbo.syn_foo_table
FOR [SRV_B].[DB1_DEV].[dbo].[foo_table]

然后可以在代码中将其称为

SELECT foo FROM dbo.syn_foo_table WHERE bar = 1 

然后您需要自定义部署脚本以创建指向环境的正确服务器/数据库的同义词。这可以使用与您在上面概述的过程类似的动态 SQL 过程，但只需要在部署时执行一次(而不是在每次执行时)。

另一种可能的解决方案是使用 SQLCMD存储过程脚本中的参数，因为 (AFAIK) VS 项目使用 SQLCMD 部署数据库对象。
此功能允许您使用 $(variablename) 形式的变量参数化 SQL 脚本 - 在您的情况下:

SELECT foo FROM [SRV_B].[$(dbname)].[foo_table] WHERE bar = 1 

变量的值可以使用环境变量设置或使用 -v 开关作为参数传递到命令中。有关完整详细信息，请参阅上面的 SQLCMD MSDN 链接。