sql-server - 如何执行很长的动态sql语句？

Question

我记得那天我会制作一整堆nvarchar(4000)变量，检查它们增长的长度，在它们填满时将它们切换出来，然后连接整个exec 调用搞乱了。我想知道是否有更简单的方法。

谢谢!

编辑:

代码示例，显示我搞砸了 case 语句

DECLARE @sql NVARCHAR(MAX)
SELECT @sql = CAST(N'SELECT ' AS NVARCHAR(MAX))

DECLARE @Index INT
SELECT @Index = 0

WHILE (@Index < 1000)
BEGIN
 SELECT @sql = CAST(@sql AS NVARCHAR(MAX)) + CAST(N'          ' AS NVARCHAR(MAX)) + CAST( CASE @Index WHEN 1 THEN N' ' END AS NVARCHAR(MAX))
 SELECT @Index = @Index + 1
END
SELECT @sql = CAST(@sql AS NVARCHAR(MAX)) + CAST(1 AS NVARCHAR(MAX))

SELECT LEN(@sql)
EXECUTE sp_executesql @sql

Answer 1

sp_executesql接受 NVARCHAR(MAX) 类型的参数，该参数最多可以增长到 2GB。不需要任何花招，因为 NVARCHAR(MAX) 类型支持所有字符串操作(连接、替换等):

[ @statement= ] statement

Is a Unicode string that contains a Transact-SQL statement or batch.

statement must be either a Unicode constant or a Unicode variable. More complex Unicode expressions, such as concatenating two strings with the + operator, are not allowed. Character constants are not allowed. If a Unicode constant is specified, it must be prefixed with an N. For example, the Unicode constant N'sp_who' is valid, but the character constant 'sp_who' is not. The size of the string is limited only by available database server memory. On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max).