sql server 解决动态字符超过8000的方法

补充下（以前也做过SP_EXECUTESQL的方法），EXEC()也可以突破这个限制， inside sql server t-programming中提到：

Concatenating Variables
In SQL Server 2000, EXEC had an advantage over sp_executesql in terms of supporting longer input code. Even though technically sp_executesql's input code string was of an NTEXT datatype, you typically wanted to construct the code string in a local variable. However, you couldn't declare a local variable with a large object type, so practically, query strings executed with sp_executesql were limited to the largest supported length of a Unicode character string (NVARCHAR), which was 4,000 characters. EXEC, on the other hand, supported a regular character (VARCHAR) input code string, allowing up to 8000 characters. Furthermore, EXEC supports a special functionality that allows you to concatenate multiple variables within the parentheses, each up to the maximum supported size of 8000 characters. For example, the following code constructs and invokes a code string that is longer than 8000 characters by concatenating three variables, generating an output with 7999 A characters and one B character:

DECLARE @sql1 AS VARCHAR(8000), @sql2 AS VARCHAR(8000), @sql3 AS VARCHAR(8000);
SET @sql1 = 'PRINT ''';
SET @sql2 = REPLICATE('A', 7999) + 'B';
SET @sql3 = ''';';
EXEC(@sql1 + @sql2 + @sql3);

However, this technique is very awkward, and it requires some acrobatics to construct code strings that are longer than 8000 characters. In SQL Server 2005, this technique is no longer necessary because you can provide the EXEC command with a variable defined as VARCHAR(MAX) or NVARCHAR(MAX) as input. Now the input string can be up to 2 GB in size, meaning over two billion characters in a VARCHAR(MAX) value, and one billion characters in NVARCHAR(MAX). For example, the following code generates a code string with 10,000 PRINT statements within it, producing a printout of all numbers in the range 1 through 10,000, each in a separate output line:

DECLARE @sql AS VARCHAR(MAX), @i AS INT;
SET @sql = '';
SET @i = 1;
WHILE @i <= 10000
BEGIN
  SET @sql = @sql + 'PRINT ' + CAST(@i AS VARCHAR(10))
    + CHAR(13) + CHAR(10);
  SET@i=@i+1;
END
EXEC(@sql);

 --------------------笔记作为记录，下次好查找