如何存储大于varchar(max)的字符串var?

问题描述

我正在尝试这样做:

DECLARE @myVar VARCHAR(MAX)
Loop with cursor
select @myVar = @myVar + bla bla bla
end loop

循环结束时，@ myVar不完整，仅包含8000个字符.

When the loop ends, @myVar is incomplete, containing only 8000 characters.

我尝试使用文本，但不允许使用本地变量.

I have tryed to use text, but is not allowed to local vars.

对这种情况有什么好的解决方案?

What would be a good solution to this case?

xml var?

我刚刚看过这篇文章:

如何在SQL Server 2000中传递大于varchar(8000)的字符串参数吗?

检查是否串联到一个varchar(max)将超出允许的最大字符数

和其他人通过网络

致谢.

推荐答案

很严重-VARCHAR(MAX)最多可以存储2 GB的数据-不只是8000个字符.....

Seriously - VARCHAR(MAX) can store up to 2 GB of data - not just 8000 characters.....

尝试一下:

DECLARE @myVar VARCHAR(MAX) = ''

DECLARE @ix INT = 1

WHILE @ix < 1000
BEGIN
    set @myVar = @myVar + CAST('bla bla bla' AS VARCHAR(MAX))
    SET @ix = @ix + 1
END

SELECT DATALENGTH(@myvar)

这将在1000次迭代后返回一个比8000个字符更高的值.

This will return a value higher than 8000 characters after 1000 iterations.

关键是:如果您使用的是varchar(max)，则需要确保始终将所有字符串显式地 cast 到varchar(max)-就像在本示例中一样.否则，SQL Server将退回到常规" varchar处理，并且实际上仅限于8000个字符....

The point is: if you're using varchar(max), you need to make sure to always cast all your strings to varchar(max) explicitly - as I did in this example. Otherwise, SQL Server will fall back to "regular" varchar processing, and that's indeed limited to 8000 characters....

这篇关于如何存储大于varchar(max)的字符串var?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！