`sp_executesql`是否真正接受`nvarchar(max)`参数？

Question

`sp_executesql`是否真正接受`nvarchar(max)`参数？

sqlsql-serversql-server-2014nvarcharlimits

7

概述: 在 @code 中的内容超过 4000 个 Unicode 字符时，EXEC sp_executesql @code 失败，但是 @code 并没有被截断为4000个字符。

我在 SQL Server 2014 Developer Edition 上观察到了这个问题。

更多细节：我的 SQL 安装脚本动态定义一些代码，因为它应该修改代码以反映环境（仅在安装期间一次）。让以下@datasource变量捕获特定环境的结果：

DECLARE @datasource nvarchar(100) = N'testdb.dbo.source_table'

@code变量声明为nvarchar(max)类型，并使用REPLACE函数根据需要修改字符串（即将占位符替换为@datasource的内容）--请参见下面的代码片段。

在Management Studio中使用@code执行sp_executesql时，会显示以下错误消息：

Msg 156, Level 15, State 1, Procedure my_sp, Line 86
关键字“AS”附近语法不正确。
Msg 102, Level 15, State 1, Procedure my_sp, Line 88
附近语法不正确“WHERE”。

下面的代码片段是准确复制以上失败的代码（以便复现）。功能可能并不重要--可能只有代码长度重要。显然，sp_executesql截断了@code的内容; 然而，实际上不应该这样做（请参见下面）：

-- ... repeated from above
DECLARE @datasource nvarchar(100) = N'testdb.dbo.source_table'

DECLARE @code nvarchar(MAX) = REPLACE(N'
-- Comment comment comment comment comment comment comment comment comment.
-- Comment comment comment comment comment comment comment comment comment.
-- Comment comment comment comment comment comment comment comment comment.
CREATE PROCEDURE dbo.my_sp
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @result int = -555   -- Comment comment comment comment comment.

    -- Comment comment comment comment comment comment comment comment comment.
    -- Comment comment comment comment comment comment comment comment comment.
    DECLARE @info_table TABLE (
        action nvarchar(10),    -- Comment comment comment comment comment
        firmaID int,            -- Comment comment comment comment comment
        kod numeric(8, 0),      -- Comment comment comment comment comment
        oz1 nvarchar(40),       -- Comment comment comment comment comment
        oz2 nvarchar(40),       -- Comment comment comment comment comment
        oz3 nvarchar(40),
        oz4 nvarchar(40)
    )

-- Comment comment comment comment comment comment comment comment comment.
    BEGIN TRANSACTION tran_firmy
    BEGIN TRY
        MERGE dbo.firmy AS target
        USING (SELECT kod, ico, dic, nazev,
               oz1, oz2, oz3, oz4,
               jeaktivni,
               ulice, mesto, psc
               FROM @datasource) AS source
        ON target.kod = source.kod
        WHEN MATCHED AND (COALESCE(target.ico, '''') != COALESCE(source.ico, '''')
                          OR COALESCE(target.dic, '''') != COALESCE(source.dic, '''')
                          OR COALESCE(target.nazev, '''') != COALESCE(source.nazev, '''')
                          OR COALESCE(target.nepouzivat_oz1, '''') != COALESCE(source.oz1, '''')
                          OR COALESCE(target.nepouzivat_oz2, '''') != COALESCE(source.oz2, '''')
                          OR COALESCE(target.nepouzivat_oz3, '''') != COALESCE(source.oz3, '''')
                          OR COALESCE(target.nepouzivat_oz4, '''') != COALESCE(source.oz4, '''')
                          OR COALESCE(target.jeaktivni, 0) != COALESCE(source.jeaktivni, 0)
                          OR COALESCE(target.ulice, '''') != COALESCE(source.ulice, '''')
                          OR COALESCE(target.mesto, '''') != COALESCE(source.mesto, '''')
                          OR COALESCE(target.psc, '''') != COALESCE(source.psc, '''')
                          ) THEN
            UPDATE
            SET target.ico = source.ico,
                target.dic = source.dic,
                target.nazev = source.nazev,
                target.nepouzivat_oz1 = source.oz1,
                target.nepouzivat_oz2 = source.oz2,
                target.nepouzivat_oz3 = source.oz3,
                target.nepouzivat_oz4 = source.oz4,
                target.jeaktivni = source.jeaktivni,
                target.ulice = source.ulice,
                target.mesto = source.mesto,
                target.psc = source.psc,
                target.changed = GETDATE(),
                target.changedby = ''dialog''
        WHEN NOT MATCHED THEN
            INSERT (kod, ico, dic, nazev,
                    nepouzivat_oz1, nepouzivat_oz2, nepouzivat_oz3, nepouzivat_oz4,
                    jeaktivni,
                    ulice, mesto, psc,
                    created, createdby)
            VALUES (source.kod, source.ico, source.dic, source.nazev,
                    source.oz1, source.oz2, source.oz3, source.oz4,
                    source.jeaktivni,
                    source.ulice, source.mesto, source.psc,
                    GETDATE(), ''dialog'')
        OUTPUT
            $action AS action,  -- INSERT or UPDATE
            inserted.ID AS firmaID,
            inserted.kod AS kod,
            inserted.nepouzivat_oz1 AS oz1,
            inserted.nepouzivat_oz2 AS oz2,
            inserted.nepouzivat_oz3 AS oz3,
            inserted.nepouzivat_oz4 AS oz4
        INTO @info_table;

        -- Comment comment comment comment comment comment comment comment comment.
        -- Comment comment comment comment comment comment comment comment comment.
        SET @result = @@ROWCOUNT

        -- Comment comment comment comment comment comment comment comment comment.
        -- Comment comment comment comment comment comment comment comment comment.
        DELETE FROM obchodni_zastupci AS ozt
        WHERE ozt.kod IN (
            SELECT kod FROM @info_table AS it WHER it.action = ''UPDATE''
            )

        -- Comment comment comment comment comment comment comment comment comment.
        -- Comment comment comment comment comment comment comment comment comment.
        UPDATE dodaci_adresy
            SET custID = f.ID
        FROM firmy AS f,  dodaci_adresy AS da
        WHERE da.custID IS NULL AND f.kod = da.kod_firmy

        COMMIT TRANSACTION tran_firmy
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION tran_firmy
        SET @result = -1  -- Comment comment comment comment comment comment comment comment comment.

    END CATCH
    RETURN @result          -- Comment comment comment comment comment comment comment comment comment.
END', N'@datasource', N'testdb.dbo.source_table')


-- The following prints only show that the full-length string is there
PRINT SUBSTRING(@code, 0, 4000)
PRINT '-----------------------------------------------------------'
PRINT SUBSTRING(@code, 4000, 10000)


EXEC sp_executesql @code

-- The following command also does not work (uncomment it).
-- EXEC(@code)

-- Even splitting to two variables and passing the concatenation 
-- does not work. 
-- DECLARE @code1 nvarchar(MAX) = SUBSTRING(@code, 0, 4000)
-- DECLARE @code2 nvarchar(MAX) = SUBSTRING(@code, 4000, 10000)
-- EXEC(@code1 + @code2)

请注意两个PRINT命令。第一个命令打印前4000个字符，第二个命令打印剩余的字符。它在一行的中间被截断，但仅用于显示@code确实包含完整的字符串。 sp_executesql (Transact-SQL)的文档说：

[ @stmt= ] statement

[...] 字符串的大小仅受可用数据库服务器内存的限制。在64位服务器上，字符串的大小限制为2 GB，即nvarchar(max)的最大值。

我在其他地方发现了使用EXEC(@code)的提示，它没有sp_executesql的限制。然而，这与上述文档的部分相矛盾。此外，EXEC(@code)也无法工作。

将替换后的相同内容复制/粘贴到SQL控制台时，它可以正常工作（也就是该过程已创建）。

如何解决此问题？

- pepr

你是在寻求解决问题的变通方法，还是像你标题中暗示的那样，询问 sp_ExecuteSQL 的内部工作原理以及它是否真正使用 nvarchar(max)？你的问题似乎是在询问后者，但你的结尾问题“如何解决这个问题？”则暗示了前者。你到底在问什么？ - Siyual

首先，我需要找到任何解决方案。其次，我需要澄清。文档说它应该有效，但它并不起作用。我已经更新了片段的结尾--替代方案也不起作用。请尝试复制/粘贴以查看。 - pepr

4个回答

0

您的查询似乎超过了nvarchar 4000的最大限制，在这种情况下，您需要将动态查询拆分为两部分。

Declare @QueryA NVARCHAR(MAX),@QueryB NVARCHAR(MAX)

SET @QueryA='SELECT * FROM'
SET @QueryB=' Employee'

EXEC (@QueryA+@QueryB)

注意：如果仍然出现相同的错误，请尝试将其分成更多部分。

- Sandip - Frontend Developer

我认为你没有理解问题的重点，但是很难确定......整个帖子似乎在问“为什么会发生这种情况”或者“它真的使用了nvarchar(max)吗？”，但最后一句话要求提供解决方法......这回答了解决方法，但我不认为这是楼主想问的问题。在他们澄清之前，很难确定。 - Siyual

谢谢，Sandip（不，我没有投反对票 :)）。我已经添加了最后一个被注释掉的代码行到代码片段中。请尝试复制/粘贴代码片段并取消注释最后几行以进行尝试。但它仍然无法工作。 - pepr

Sandip -- 你应该在这里加上一句话："是的，它接受NVARCHAR(MAX)，但如果超过4000个字符，没有解决方法就无法正常工作。" - Mark Sowul

我是一个获得踩票的人，阅读我的回答以了解原因。我已经测试了EXEC和sp_executesql两种方法，使用100,000个NVARCHAR字符从2008年开始都没有任何问题。 - Matt

0

我不知道为什么会出现这个错误：

Msg 156, Level 15, State 1, Procedure my_sp, Line 86
Incorrect syntax near the keyword 'AS'.
Msg 102, Level 15, State 1, Procedure my_sp, Line 88
Incorrect syntax near 'WHERE'.

被解释为可能是您的字符串长度过长。这显然是一个语法错误。正如Edmon指出的那样，您犯了两个错误。

无论如何，我发布这个答案是为了打破另一个答案和您在问题中提出的建议所创造的谬论，即长度成为问题的原因是因为您的语句超过了4,000个字符。这里有一个脚本，可以生成一个100,000个字符长度的NVARCHAR SQL语句，并将其作为EXEC (@SQL)和sp_executeSQL执行。它们都没有在SQL 2008 SP4-OD 10.0.6547.0（x64）上执行时出现问题，也没有在2014 SP2上出现问题。

因此，至少从那个2008版本开始，似乎没有问题，也不需要任何解决方法。

DECLARE @CharacterLength INT = 100000
DECLARE @SQL NVARCHAR(MAX) = 'SELECT ' + CHAR(39)

DECLARE @i INT = 1

WHILE (LEN(@SQL) <= @CharacterLength - 2)
BEGIN

    SET @SQL = @SQL + 'A'

END

SET @SQL = @SQL + CHAR(39)

PRINT 'Total Length: ' + CAST(LEN(@SQL) AS VARCHAR(100))

EXECUTE sp_executesql @sql

PRINT 'No Problem with sp_executesql'

BEGIN TRY
    PRINT 'Total Length: ' + CAST(LEN(@SQL) AS VARCHAR(100))
    EXEC (@SQL)
    PRINT 'No Problem with EXEC (@SQL)'
END TRY
BEGIN CATCH
    PRINT 'Yep never got here because there was no problem with over this character limit'
END CATCH

- Matt

-2

这个问题困扰了我一段时间。对我有效的解决方案是不要声明超过一个 NVARCHAR(MAX) 变量。

在构建动态 SQL 时，您可能会为将合并到最终 SQL 查询变量中并传递给 sp_executesql 的子字符串使用 NVARCHAR(MAX)。

NVARCHAR(MAX) 的最大内存分配为 2GB。您的服务器可能针对每个声明的 NVARCHAR(MAX) 分配了全部 2GB。如果您声明了三个 NVARCHAR(MAX) 变量，则您的服务器可能分配 6GB 来执行脚本。这可能足以在运行时过载您的 RAM，具体取决于其他正在执行的操作。

如果您知道这些子字符串都不会超过 8,000 个字符，请改用 VARCHAR(8000) 而不是 NVARCHAR(MAX)。只需在最终字符串变量（其中组合了所有子字符串变量）中使用 NVARCHAR(MAX)，然后将其传递到 sp_executesql 中。

这就是为我解决此问题的方法。

- AThrillOfHope

2

不，服务器并不会为每个最大变量分配2GB的RAM。无论你遇到了什么问题，你显然都误解了正在发生的事情。 - Martin Smith

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Edmond Quinton · Accepted Answer

sp_executesql接受 NVARCHAR(MAX)。问题在于查询模板中以下语句存在错误：

    DELETE FROM obchodni_zastupci AS ozt
    WHERE ozt.kod IN (
        SELECT kod FROM @info_table AS it WHER it.action = ''UPDATE''
        )

应该是这样的：如下所示：

    DELETE FROM obchodni_zastupci
    WHERE obchodni_zastupci.kod IN (
        SELECT kod FROM @info_table AS it WHERE it.action = ''UPDATE''
        )

完整的查询应如下所示：

DECLARE @datasource nvarchar(100) = N'testdb.dbo.source_table'
DECLARE @template NVARCHAR(MAX) = N'
-- Comment comment comment comment comment comment comment comment comment.
-- Comment comment comment comment comment comment comment comment comment.
-- Comment comment comment comment comment comment comment comment comment.
CREATE PROCEDURE dbo.my_sp
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @result int = -555   -- Comment comment comment comment comment.

    -- Comment comment comment comment comment comment comment comment comment.
    -- Comment comment comment comment comment comment comment comment comment.
    DECLARE @info_table TABLE (
        action nvarchar(10),    -- Comment comment comment comment comment
        firmaID int,            -- Comment comment comment comment comment
        kod numeric(8, 0),      -- Comment comment comment comment comment
        oz1 nvarchar(40),       -- Comment comment comment comment comment
        oz2 nvarchar(40),       -- Comment comment comment comment comment
        oz3 nvarchar(40),
        oz4 nvarchar(40)
    )

-- Comment comment comment comment comment comment comment comment comment.
    BEGIN TRANSACTION tran_firmy
    BEGIN TRY
        MERGE dbo.firmy AS target
        USING (SELECT kod, ico, dic, nazev,
               oz1, oz2, oz3, oz4,
               jeaktivni,
               ulice, mesto, psc
               FROM @datasource) AS source
        ON target.kod = source.kod
        WHEN MATCHED AND (COALESCE(target.ico, '''') != COALESCE(source.ico, '''')
                          OR COALESCE(target.dic, '''') != COALESCE(source.dic, '''')
                          OR COALESCE(target.nazev, '''') != COALESCE(source.nazev, '''')
                          OR COALESCE(target.nepouzivat_oz1, '''') != COALESCE(source.oz1, '''')
                          OR COALESCE(target.nepouzivat_oz2, '''') != COALESCE(source.oz2, '''')
                          OR COALESCE(target.nepouzivat_oz3, '''') != COALESCE(source.oz3, '''')
                          OR COALESCE(target.nepouzivat_oz4, '''') != COALESCE(source.oz4, '''')
                          OR COALESCE(target.jeaktivni, 0) != COALESCE(source.jeaktivni, 0)
                          OR COALESCE(target.ulice, '''') != COALESCE(source.ulice, '''')
                          OR COALESCE(target.mesto, '''') != COALESCE(source.mesto, '''')
                          OR COALESCE(target.psc, '''') != COALESCE(source.psc, '''')
                          ) THEN
            UPDATE
            SET target.ico = source.ico,
                target.dic = source.dic,
                target.nazev = source.nazev,
                target.nepouzivat_oz1 = source.oz1,
                target.nepouzivat_oz2 = source.oz2,
                target.nepouzivat_oz3 = source.oz3,
                target.nepouzivat_oz4 = source.oz4,
                target.jeaktivni = source.jeaktivni,
                target.ulice = source.ulice,
                target.mesto = source.mesto,
                target.psc = source.psc,
                target.changed = GETDATE(),
                target.changedby = ''dialog''
        WHEN NOT MATCHED THEN
            INSERT (kod, ico, dic, nazev,
                    nepouzivat_oz1, nepouzivat_oz2, nepouzivat_oz3, nepouzivat_oz4,
                    jeaktivni,
                    ulice, mesto, psc,
                    created, createdby)
            VALUES (source.kod, source.ico, source.dic, source.nazev,
                    source.oz1, source.oz2, source.oz3, source.oz4,
                    source.jeaktivni,
                    source.ulice, source.mesto, source.psc,
                    GETDATE(), ''dialog'')
        OUTPUT
            $action AS action,  -- INSERT or UPDATE
            inserted.ID AS firmaID,
            inserted.kod AS kod,
            inserted.nepouzivat_oz1 AS oz1,
            inserted.nepouzivat_oz2 AS oz2,
            inserted.nepouzivat_oz3 AS oz3,
            inserted.nepouzivat_oz4 AS oz4
        INTO @info_table;

        -- Comment comment comment comment comment comment comment comment comment.
        -- Comment comment comment comment comment comment comment comment comment.
        SET @result = @@ROWCOUNT

        -- Comment comment comment comment comment comment comment comment comment.
        -- Comment comment comment comment comment comment comment comment comment.
        DELETE FROM obchodni_zastupci
        WHERE obchodni_zastupci.kod IN (
            SELECT kod FROM @info_table AS it WHERE it.action = ''UPDATE''
            )

        -- Comment comment comment comment comment comment comment comment comment.
        -- Comment comment comment comment comment comment comment comment comment.
        UPDATE dodaci_adresy
            SET custID = f.ID
        FROM firmy AS f,  dodaci_adresy AS da
        WHERE da.custID IS NULL AND f.kod = da.kod_firmy

        COMMIT TRANSACTION tran_firmy
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION tran_firmy
        SET @result = -1  -- Comment comment comment comment comment comment comment comment comment.

    END CATCH
    RETURN @result          -- Comment comment comment comment comment comment comment comment comment.
END'


DECLARE @code nvarchar(MAX) = REPLACE(@template, N'@datasource', N'testdb.dbo.source_table');

exec (@code);