如何执行非常长的动态sql语句？ [英] How do I execute a very long dynamic sql statement?

问题描述

我记得有一天，我会制作一整包 nvarchar（4000） var，检查它们的长度，并在填充时将其切换出起来，然后将整个混乱串联起来，以供exec调用。我想知道是否有更简单的方法。

I remember back in the day I would make a whole wack of nvarchar(4000) vars, check the length of them as they grew, switch them out as they filled up and then concatenate the whole mess together for the exec call. I was wondering if there was an easier way of doing it.

谢谢！

编辑：

代码示例，向我展示了case语句的错误

Code Sample, shows me screwing up the case statement

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

推荐答案

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

sp_executesql accepts a parameter of type NVARCHAR(MAX) which can grow up to 2GB. There is no need for any gimmick, since the NVARCHAR(MAX) type supports all the string operations (concatenation, replacing etc):

[@ statement =]语句

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

声明必须是Unicode
常量或Unicode变量。不允许使用更多
复杂Unicode表达式，例如
用+
运算符连接两个字符串。不允许使用字符
常量。如果指定了
Unicode常量，则必须在
前面加上N。例如，
Unicode常量N'sp_who'是
有效，但字符常量
'sp_who'不是。
字符串的大小仅受可用的
数据库服务器内存限制。在64位
服务器上，字符串的大小为
，上限为2 GB，最大大小为
nvarchar（max）。

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).

这篇关于如何执行非常长的动态sql语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！