exec limited to 4000 characters?

Question

trying to execute this script gives only the first select as a result

declare @sp nvarchar(max)
select @sp = concat(N'select 1 ', replicate('-', 5000), char(13) + char(10), N'select 2')
exec (@sp)

but when I lower hyphens to 1000, I get also the second select How can I overcome this limit?

Answer 1

Your problem is with the CONCAT.

As documented, the return type depends on the type of the parameters. With a mixture of short nvarchar and varchar literal strings, your CONCAT will return nvarchar with some maximum length less than or equal to 4000.

A rewrite to use a max data type and consistent data types otherwise functions as you desire:

DECLARE @sp nvarchar(max);
SELECT @sp = CONCAT
    (
        CONVERT(nvarchar(max), N'select 1 '), 
        REPLICATE(N'-', 5000), 
        NCHAR(13) + NCHAR(10), 
        N'select 2'
    );
EXECUTE (@sp);

Online db<>fiddle demo