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

发布于 2024-09-08 02:53:51 字数 627 浏览 8 评论 0原文

我记得当时我会制作一整堆 nvarchar(4000) 变量，在它们增长时检查它们的长度，在它们填满时将它们切换出来，然后将整个混乱连接在一起执行调用。我想知道是否有更简单的方法可以做到这一点。

谢谢！

编辑：

代码示例，显示我搞砸了 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

原文

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.

Thanks!

Edit:

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

屌丝范 2024-09-15 02:53:51

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

[@statement=]语句
是包含 Transact-SQL 语句或批处理的 Unicode 字符串。
语句必须是 Unicode
常量或 Unicode 变量。更多的
复杂的 Unicode 表达式，例如
用 + 连接两个字符串
运营商，不允许。特点
不允许使用常量。如果一个
指定了 Unicode 常量，它必须
以 N 为前缀。例如，
Unicode 常量 N'sp_who' 是
有效，但字符常量
'sp_who' 不是。尺寸
字符串仅受可用的限制
数据库服务器内存。在 64 位上
服务器，字符串的大小是
限制为 2 GB，最大大小为
nvarchar（最大值）。

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