T-SQL VARCHAR(MAX) 被截断

Question

DECLARE @str VARCHAR (MAX);

SELECT @str = COALESCE(@str + CHAR(10), '') +
       'EXECUTE CreateDeno ' + CAST(ID AS VARCHAR) 
FROM   GL_To_Batch_Details
WHERE  TYPE = 'C' AND
       Deno_ID IS NULL;

--PRINT @str;--SELECT @str;
**EXEC(@str);**

已编辑

EXECUTE语句是否像PRINT一样将字符串截断为8,000个字符？如何执行超过8,000个字符的动态SQL语句？

非常感谢任何建议。

- Nick Binnet

---

我认为你有点困惑，你不能打印超过8000个字符，这并不意味着@str被截断。 - Lamak

我已经编辑了问题，以更具体地描述问题。实际上，在 EXECUTE 产生错误时，我使用 PRINT 查看了动态生成的 T-SQL。在两种情况下，nvarchar(MAX) 值都被截断了。有什么想法吗？ - Nick Binnet

Answer 1

PRINT的输出长度被限制在8k。

在SSMS结果窗格中也存在8k的限制。

前往

工具 -> 选项 -> 查询结果

查看选项。

要验证实际数据的长度，请检查：

SELECT LEN(@str)

- JNK

---

好的。这里使用PRINT而不是EXEC，以查看实际问题是什么。 - Nick Binnet

@Nick Bennet: MSDN says：在早期版本的SQL Server中，字符串限制为8,000字节。这需要将大字符串连接起来进行动态执行。在SQL Server 2005中，可以指定varchar(max)和nvarchar(max)数据类型，允许字符串达到2GB的数据。 - Andomar

我不得不使用NVARCHAR(MAX)数据类型和sp_executesql才能使其正常工作。请问是否可以在不使用sp_executesql的情况下实现相同的功能？谢谢。 - Nick Binnet

Answer 2

当连接字符串且结果类型为VARCHAR(MAX)并且超过8000个字符时，至少需要一个参数和/或在连接中使用的元素为VARCHAR(MAX)类型，否则将在结果字符串中发生截断，并且在EXEC语句中无法执行。

示例：

DECLARE @sql AS VARCHAR(MAX);
/* DECLARE @someItem AS VARCHAR(100); -- WILL CAUSE TRUNCATION WHEN @sql HAS LEN > 8000 */
DECLARE @someItem AS VARCHAR(MAX); -- All string variables need to be VARCHAR(MAX) when concatenating to another VARCHAR(MAX)

SET @someItem = 'Just assume the resulting @sql variable goes over 8000 characters...';
SET @sql = 'SELECT Something FROM Somewhere WHERE SomeField = ''' + @someItem + '''';

EXEC (@sql);
--PRINT @sql;

更多信息请参阅MSDN。

"如果字符串连接的结果超过8,000个字节的限制，则结果将被截断。但是，如果连接的字符串中至少有一个是大型值类型，则不会发生截断。"

- WayneJohn

Answer 3

PRINT命令的长度限制为8000个字符，无论输出长度如何（或是否为varchar(max)）。为了解决这个问题，您需要将字符串分块输出，每块长度小于<8000。

---

更新：针对您的编辑，exec不限制字符串长度。我已经准备了以下示例来证明这一点：

DECLARE @str VARCHAR (MAX);


;WITH CTE_Count AS
(
    select counter = 1
    union all
    select counter = counter+1
    from CTE_Count
    Where counter < 2000

)
SELECT             
    @str=COALESCE(@str + CHAR (10) ,
        '' ) + 'select value=' + CAST (counter AS VARCHAR) 
from
    CTE_Count

Option (MAXRECURSION 0)

PRINT len(@str);--SELECT @str;

exec (@str)

运行此代码将输出长度为34892个字符，并且会执行所有2000个执行语句（请注意，可能需要几分钟时间！）

- Jon Egerton

Answer 4

varchar的默认长度为30个字符：

CAST (ID AS VARCHAR) 

这个id可能会超过30个字符吗？

- Andomar

---

不，ID长度超过30个字符是不可能的。 - Nick Binnet

永远不要使用默认长度是一个好的编程实践，但有时默认长度为1也是可以的。 - HLGEM

Answer 5

我也想看看我发送给Exec的内容，但是PRINT限制让我感到困惑。不得不编写一个过程来分块打印。

CREATE PROCEDURE [dbo].[KFX_PrintVarcharMax] 
    @strMax varchar(max)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE 
        @index int = 0,
        @start int = 1,
        @blkSize int = 2000;


    WHILE @Start < LEN(@strMax)
    BEGIN
        IF @start + @blkSize >= LEN(@strMax)
        BEGIN
            -- If remainder is less than blocksize print the remainder, and exit.
            PRINT SUBSTRING(@strMax, @start, @blkSize)
            BREAK;
        END
        -- Else find the next terminator (beyond the blksize)
        SET @index = CHARINDEX(CHAR(10), @strMax, @start + @blkSize);
        if @index >= @start
        BEGIN
            PRINT SubString(@strMax, @start, @index - @start + 1)
            SET @start = @index + 1;
            SET @blkSize  = CASE WHEN @start + 2000 < LEN(@strMax) THEN 2000 
                            ELSE LEN(@strMax) - @start + 1 END
        END
        ELSE
        BEGIN
            -- No char(10) found.  Just print the rest.
            PRINT SUBSTRING(@strMax, @start, LEN(@strMax))
            BREAK;
        END 
    END

END

- Darrel Lee

Answer 6

当您连接字面量时，如果其中一个不是varchar（max），则结果将被“隐式转换”为varchar（8000）。

要生成文字varchar（max），所有部分都必须是varchar（max）。 注意：我在更新varchar（max）列时遇到了这种情况，从未使用EXEC命令进行过测试。

另外，如先前的答案中所述，打印命令具有限制，但您可以尝试选择该变量而不是打印它。（还有一个在MS-SMS上可以配置的选择长度限制）

- jean