nvarchar(max) 仍然被截断 [英] nvarchar(max) still being truncated

问题描述

所以我在 MS SQL Server 2008 中编写了一个存储过程.这是一个非常长的查询，我必须动态编写它，所以我创建了一个名为 @Query 的变量并将其设为类型NVARCHAR(MAX).现在，有人告诉我在现代版本的 SQL Server 中，NVARCHAR(MAX) 可以容纳大量数据，远远超过最初的 4000 个字符的最大值.但是，当我尝试打印时 @Query 仍然被截断为 4000 个字符.

So I'm writing a stored procedure in MS SQL Server 2008. It's a really long query and I have to write it dynamically, so I create a variable called @Query and make it of type NVARCHAR(MAX). Now, I have been told that in modern versions of SQL Server, NVARCHAR(MAX) can hold a ridiculous amount of data, way more than the original 4000 character maximum. However, @Query is still getting truncated to 4000 characters when I try to print it out.

DECLARE @Query NVARCHAR(max);
SET @Query = 'SELECT...' -- some of the query gets set here
SET @Query = @Query + '...' -- more query gets added on, etc.

-- later on...
PRINT LEN(@Query) -- Prints out 4273, which is correct as far as I can tell
PRINT @Query      -- Truncates value to 4000 characters
EXEC sp_executesql @Query -- totally crashes due to malformed (truncated) query

我是否做错了什么，还是我对 NVARCHAR(MAX) 的工作方式完全错误?

Am I doing something incorrectly, or am I completely wrong about how NVARCHAR(MAX) works?

推荐答案

要查看生成的动态 SQL，请更改为文本模式(_快捷键: Ctrl-T)，然后使用 SELECT

To see the dynamic SQL generated, change to text mode (_shortcut: Ctrl-T), then use SELECT

PRINT LEN(@Query) -- Prints out 4273, which is correct as far as I can tell
--SET NOCOUNT ON
SELECT @Query

至于 sp_executesql，试试这个(在文本模式下)，它应该显示三个 aaaaa... 中间一个是最长的 'SELECT ..' 添加.观察右下角状态栏中的 Ln... Col.. 指示器，在第二个输出结束时显示 4510.

As for sp_executesql, try this (in text mode), it should show the three aaaaa...'s the middle one being the longest with 'SELECT ..' added. Watch the Ln... Col.. indicator in the status bar at bottom right showing 4510 at the end of the 2nd output.

declare @n nvarchar(max)
set @n = REPLICATE(convert(nvarchar(max), 'a'), 4500)
SET @N = 'SELECT ''' + @n + ''''
print @n   -- up to 4000
select @n  -- up to max
exec sp_Executesql @n

这篇关于nvarchar(max) 仍然被截断的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！