Действительно ли `sp_executesql` принимает аргумент `nvarchar(max)`?

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

Ваш запрос выглядит так, как будто он превышает максимальный предел nvarchar 4000, в таких случаях вам необходимо разделить динамический запрос на две части.

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

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

EXEC (@QueryA+@QueryB)

Примечание. Если по-прежнему возникает та же ошибка, попробуйте разделить ее на несколько частей.

Я понятия не имею, почему ошибка:

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

был интерпретирован как возможно, что длина вашей строки слишком длинная. Это явно синтаксическая ошибка. у вас было 2 ошибки, как указал Эдмон.

В любом случае, я публикую этот ответ, чтобы развеять миф, созданный другим ответом, и ваше предположение в вашем вопросе о том, что длина является проблемой, потому что ваше утверждение превышает 4,000 символов. вот скрипт для создания оператора SQL длиной 100,000 символа NVARCHAR и выполнения его как 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

Эта же ситуация меня немного огорчила. И решение для меня заключалось в том, чтобы не объявлять более одной переменной NVARCHAR(MAX).

При построении динамического SQL вы можете использовать NVARCHAR(MAX) для подстрок, которые объединяются в окончательную переменную запроса SQL, которая передается в sp_executesql.

NVARCHAR(MAX) имеет МАКСИМАЛЬНОЕ выделение памяти 2 ГБ. Ваш сервер может разграничивать заявленные полные 2 ГБ НА NVARCHAR(MAX). Если у вас есть, скажем, три объявленных переменных NVARCHAR(MAX), ваш сервер может выделить 6 ГБ для выполнения вашего скрипта. Этого может быть достаточно, чтобы перегрузить вашу оперативную память, в зависимости от того, что еще выполняется во время выполнения.

Если вы знаете, что все подстроки будут иметь длину менее 8000 символов, используйте VARCHAR(8000) вместо NVARCHAR(MAX) для подстрок. Просто используйте NVARCHAR(MAX) для окончательной строковой переменной (где объединены все переменные подстроки), которая передается в sp_executesql.

Это то, что решило эту проблему для меня.