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屋！