sql-server - 如何执行长动态查询(大于 4000)字符 - 再次

Question

注意:我在 SQL Server 2008 R2 下运行...

我花时间阅读了该站点和其他站点上的数十篇关于如何执行查询超过 4000 个字符的动态 SQL 的帖子。我已经尝试了十多种提出的解决方案。共识似乎是将查询拆分为 4000 个字符的变量，然后执行以下操作:

EXEC (@SQLQuery1 + @SQLQuery2)

这对我不起作用 - 查询在@SQLQuery1 的末尾被截断。

现在，我已经看到了人们如何通过使用 REPLICATE 一堆空格等来“强制”长查询的示例，但这是一个真正的查询 - 但它变得比这更复杂一些。

我有一个名为“Company_A_ItemView”的 SQL View 。

我有 10 家公司，我想用不同的名称创建完全相同的 View ，例如
"Company_B_ItemView"
"Company_C_ItemView"
..等等。

如果您提供帮助，请不要问为什么有多个 View - 只要接受我需要这样做，好吗？

每个公司都有自己的一组表，CREATE VIEW 语句按名称引用多个表。这是简要示例，但请记住，查询的总长度约为 6000 个字符:

CREATE view [dbo].[Company_A_ItemView] as  
select  
WE.[Item No_],  
WE.[Location Code],   
    LOC.[Bin Number],  
[..more fields, etc.]  
from  
[Company_A_Warehouse_Entry] WE  
left join  
[Company_A_Location] LOC  

...你明白了

所以，我目前正在做的是:

一种。将 CREATE VIEW 语句的内容提取到 2 个声明的变量中，例如

Set @SQLQuery1 = (select text 
                  from syscomments 
                  where ID = 1382894081 and colid = 1)  
Set @SQLQuery2 = (select 
                  from syscomments 
                  where ID = 1382894081 and colid = 2)

请注意，这是 SQL 存储长定义的方式 - 当您创建 View 时，它会将文本存储到多个 syscomments 记录中。在我的例子中， View 被分成 3591 个字符的文本块，放在第一个 syscomment 记录中，其余的文本在第二个记录中。我不知道为什么 SQL 不在 syscomment 字段中使用所有 4000 个字符。并且语句在单词中间被中断。

请注意，在我的所有示例中，所有@SQLQueryxxx 变量都声明为 varchar(max)。我也尝试将它们声明为 nvarchar(max) 和 varchar(8000) 和 nvarchar(8000) ，结果相同。

湾然后我对“Company_A”执行“搜索和替换”并将其替换为“Company_B”。在下面的代码中，变量“@CompanyID”首先设置为“Company_B”:

SET @SQLQueryNew1 = @SQLQuery1   
SET @SQLQueryNew1 = REPLACE(@SQLQueryNew1, 'Company_A', @CompanyID)  
SET @SQLQueryNew2  = @SQLQuery2   
SET @SQLQueryNew2 = REPLACE(@SQLQueryNew2, 'Company_A',@CompanyID)

C。然后我尝试:

EXEC (@SQLQueryNew1 + @SQLQueryNew2)

返回的消息表明它正在尝试执行在@SQLQueryNew1 末尾截断的语句，例如查询文本的 80%(大约)。

我已经尝试将最终结果转换为新的 varchar(max) 和 nvarchar(max) - 没有运气
我试过 CAST'ing 原始查询一个新的 varchar(max) 和 nvarchar(max) - 没有运气

我查看了检索原始 CREATE VIEW 语句的结果，它很好。

我尝试了各种其他方法来检索原始 CREATE VIEW 语句，例如:

Set @SQLQuery1 = (select VIEW_DEFINITION) 
                  FROM [MY_DATABASE].[INFORMATION_SCHEMA].[VIEWS] 
                  where TABLE_NAME = 'Company_A_ItemView')`

这个只返回 CREATE VIEW 的前 4000 个字符

Set @SQLQuery1 = (SELECT (OBJECT_DEFINITION(@ObjectID))

如果我做一个

SELECT LEN(OBJECT_DEFINITION(@ObjectID))

它返回查询的正确长度(例如 5191)，但如果我查看@SQLQuery1，或尝试

EXEC(@SQLQuery1), the statement is still truncated.

C。有一些引用文献指出，由于我在检索查询文本后对其进行了操作，因此结果变量将被截断为 4000 个字符。我在执行 REPLACE 时尝试过 CAST'ing 结果，例如

SET @SQLQueryNew1 = SELECT (CAST(REPLACE(@SQLQueryNew1, 
                                 'Company_A', 
                                 @CompanyID) AS varchar(max))

结果一样。

我知道还有其他方法，例如创建用于创建 View 的存储过程。但是 View 正在开发中并且有些“不断变化”，因此将 CREATE VIEW 的文本放在存储过程中很麻烦。我的目标是能够获取 Company_A 的 View 并精确复制它 - 多次，除了引用 Company_B 的 View 名称和表名称、Company_C 的 View 名称和表名称等。

我想知道是否有人对长 SQL“CREATE VIEW”语句进行了这种类型的操作并尝试执行它。

Answer 1

只需使用 VARCHAR(MAX) 或 NVARCHAR(MAX) .它们适用于 执行(字符串) .

供引用，

Note that this is how SQL stores long definitions - when you create the view, it stores the text into multiple syscomments records.

这是不正确的。这是过去在 SQL Server 2000 上的做法。从 SQL Server 2005 及更高版本开始，它们在 中的单个条目中保存为 NVARCHAR(MAX)。 sys.sql_modules .

系统评论 仍然存在，但它被保留为只读，只是为了兼容性。

所以你需要做的就是改变你的@SQLQuery1、2等。变量到单个 NVARCHAR(MAX) 变量，然后从 sys.sql_modules 表的 [definition] 列中提取您的 View 代码。

请注意，您应该小心您的字符串操作，因为如果所有输入参数都不是 (N)VARCHAR(MAX)，某些函数将恢复为 (N)VARCHAR(4000) 输出。 (抱歉，我不知道是哪个，但 REPLACE() 可能是一个)。事实上，这可能是导致您的测试如此困惑的原因。